package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.Course;
import com.xmy.cultivate.entity.OrderDetail;
import com.xmy.cultivate.entity.Organization;
import com.xmy.cultivate.entity.ResidueStudent;
import com.xmy.cultivate.entity.response.ArrivalRate;
import com.xmy.cultivate.entity.response.ContinueInfo;
import com.xmy.cultivate.entity.response.YearAndQuarter;
import com.xmy.cultivate.entity.views.*;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 订单报名信息 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-08-01
 */
@Mapper
public interface OrderDetailMapper01 extends BaseMapper<OrderDetail> {

    /**
     * 报读列表
     * @return
     */
    @Select("select * from enroll_view ${ew.customSqlSegment}")
    public abstract IPage<EnrollList> getEnrollList(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select sum(solid_price) as solid_price from enroll_view ${ew.customSqlSegment}")
    public abstract BigDecimal getSolidPrice(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select count(DISTINCT student_id) as count  from enroll_view ${ew.customSqlSegment}")
    public abstract BigDecimal getAllStudentCount(@Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select id,order_id,all_course_count,use_course_count,residue_course_count,use_price,out_price,validity_date,price,solid_price,discounts_price,use_price from order_detail ${ew.customSqlSegment}")
    public abstract List<OrderDetail> orderDetail(@Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select id,order_id,all_course_count,use_course_count,residue_course_count,use_price,out_price,validity_date,price,solid_price,discounts_price,use_price from order_detail where school_id = #{schoolId} and student_id = #{studentId} and course_id = #{courseId} and deleted = 0 and status<>2 and residue_course_count>0")
    public abstract List<OrderDetail> orderDetailListForCourse(Long schoolId,Long studentId,Long courseId);


    //由于要纠正数据，等于0的限制暂时取消，后面恢复，and residue_course_count>0
    @Select("select id,order_id,course_price_id,all_course_count,use_course_count,residue_course_count,use_price,out_price,validity_date,price,solid_price,discounts_price,residue_price,student_id from order_detail where school_id = #{schoolId} and student_id = #{studentId} and subjects_id = #{subjectsId} and deleted = 0 and status<>2 and residue_course_count>0 and year_and_quarter>=${yearAndQuarter} and lesson_type=${lessonType} order by year_and_quarter,id asc")
    public abstract List<OrderDetail> orderDetailListForSubjects(@Param("schoolId")Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType")Integer lessonType);



    //public abstract List<Map<String,Object>> orderDetail(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 获取该科目是否报读过
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select count(a.id) as count from order_detail as a left join order_apply as b on a.order_id = b.id left join course as c on a.course_id = c.id where a.school_id = #{schoolId} and a.student_id = #{studentId} and c.subjects_id = #{subjectsId} and b.status <> 2 and a.deleted = 0 and ((a.year_part<#{newYeartPart}) or (a.year_part=#{newYeartPart} and a.quarter_num<=#{newNum})) and a.reading_status<>3")
    public  abstract  Integer getApplyCountForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("newYeartPart") Integer newYeartPart,@Param("newNum") Integer newNum);

    @Select("select a.id,a.year_part as yearPart,a.is_new_student as isNewStudent,c.quarter_num as quarterNum from order_detail as a left join order_apply as b on a.order_id = b.id left join course as c on a.course_id = c.id where a.school_id = #{schoolId} and a.student_id = #{studentId} and c.subjects_id = #{subjectsId} and b.status <> 2 and a.deleted = 0 and ((a.year_part<#{newYeartPart}) or (a.year_part=#{newYeartPart} and a.quarter_num<=#{newNum})) and a.reading_status<>3 ORDER BY a.created_at desc limit 0,1 ")
    public  abstract  Map<String,Object> getApplyCourseForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("newYeartPart") Integer newYeartPart,@Param("newNum") Integer newNum);


    /**
     * 获取某科目最后一次报读的数据
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select a.* from order_detail as a left join order_apply as b on a.order_id = b.id left join course as c on a.course_id = c.id where a.student_id = #{studentId} and a.subjects_id=#{subjectsId} and b.status <> 2 and a.deleted = 0 order by a.id desc limit 0,1")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "course",
                    column = "course_id",
                    javaType = Course.class,
                    one = @One(select = "com.xmy.cultivate.mapper.CourseMapper.selectById")
            )
    })
    public abstract OrderDetail getLastOneForSubjectId(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId);


    /**
     * 获取所报读科目的剩余课时
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count from order_detail where deleted=0 and status<>2 and school_id=#{schoolId} and student_id=#{studentId} and subjects_id=#{subjectsId}")
    public abstract Integer getResidueCourseCountForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId);


    /**
     * 获取报读课程的最后一条数据
     * @param schoolId
     * @param studentId
     * @param courseId
     * @return
     */
    @Select("select a.* from order_detail as a where a.school_id = #{schoolId}  a.student_id = #{studentId} and a.course_id=#{courseId} and b.status <> 2 and a.deleted = 0 and a.residue_course_count>0 order by a.id desc limit 0,1")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "course",
                    column = "course_id",
                    javaType = Course.class,
                    one = @One(select = "com.xmy.cultivate.mapper.CourseMapper.selectById")
            )
    })
    public abstract OrderDetail getLastOneForCourseId(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("courseId") Long courseId);


    /**
     * 获取科目的剩余课次
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @param yearAndQuarter
     * @return
     */
    @Select("select IFNULL(sum(a.residue_course_count/3),0) as residueCourseCount  from order_detail as a left join grade as b on a.grade_id = b.id where a.school_id= #{schoolId} and a.student_id = #{studentId} and a.subjects_id = #{subjectsId} and a.status <>2 and a.year_and_quarter >= #{yearAndQuarter} and a.lesson_type=${lessonType} and a.deleted = 0 ")
    public abstract Integer getResidueCourseCount(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);



    /**
     * 获取科目的剩余课时,未除3的
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select IFNULL(sum(a.residue_course_count),0) as residueCourseCount  from order_detail as a left join grade as b on a.grade_id = b.id where a.school_id= #{schoolId} and a.student_id = #{studentId} and a.subjects_id = #{subjectsId} and a.status <>2 and a.deleted = 0 ")
    public abstract Integer getResidueCourseCountNoSub(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId);



    /**
     * 获取所报读班级的剩余课时
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count,sum(residue_price) as residue_price from order_detail as a where id in (select order_detail_id from student_grade as b where b.student_id=#{studentId} and b.grade_id=#{gradeId} and b.status =1 and b.reading_status=1 and b.school_id=#{schoolId}) and a.reading_status = 1 and a.deleted = 0")
    public abstract OrderDetailTiny getResidueCourseCountForGrade(@Param("studentId") Long studentId,@Param("gradeId") Long gradeId,@Param("schoolId") Long schoolId);

    /**
     * 获取所报读课程的剩余课时
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count,sum(all_course_count) as all_course_count,sum(use_course_count) as use_course_count,sum(use_price) as use_price from order_detail as a where student_id=#{studentId} and course_id=#{courseId} and a.reading_status = 1 and a.school_id = #{schoolId} and a.deleted = 0 and a.status<>2")
    public abstract OrderDetailTiny getResidueCourseCountForCourseId(@Param("studentId") Long studentId,@Param("courseId") Long courseId,@Param("schoolId") Long schoolId);

    /**
     * 获取所报读科目的剩余课时
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count,sum(all_course_count) as all_course_count,sum(use_course_count) as use_course_count,sum(use_price) as use_price from order_detail as a where student_id=#{studentId} and subjects_id=#{subjectsId} and a.reading_status = 1 and a.school_id = #{schoolId} and a.deleted = 0 and a.status<>2")
    public abstract OrderDetailTiny getResidueCourseCountForSubjectsId(@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("schoolId") Long schoolId);



    /**
     * 获取所报读课程的剩余课时
     * @return
     */
    @Select("select sum(residue_course_count) as residue_course_count from order_detail as a where student_id=#{studentId} and course_id=#{courseId} and a.reading_status = 1 and a.school_id = #{schoolId} and a.deleted = 0 and a.status<>2")
    public abstract Integer getResidueCourseHouseForCourseId(@Param("studentId") Long studentId,@Param("courseId") Long courseId,@Param("schoolId") Long schoolId);

    /**
     * 办理中心获取学员报读信息
     * @return
     */
    //@Select("select a.* from order_detail as a LEFT JOIN order_apply as b on a.order_id = b.id where b.`status` <> 2  and a.residue_course_count > 0 and a.student_id = #{studentId} and a.deleted=0  group by course_id order by a.id desc ")
    @Select("select a.* from order_detail as a LEFT JOIN order_apply as b on a.order_id = b.id ${ew.customSqlSegment} ")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "course",
                    column = "course_id",
                    javaType = Course.class,
                    one = @One(select = "com.xmy.cultivate.mapper.CourseMapper.selectById")
            ),
            @Result(
                    property = "organizationModel",
                    column = "school_id",
                    javaType = Organization.class,
                    one = @One(select = "com.xmy.cultivate.mapper.OrganizationMapper.selectById")
            )
    })
    public abstract List<OrderDetail> GetLessonByStuInfoId(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 报读通过课程，获取在读信息
     * @param courseId
     * @param studentId
     * @return
     */
    @Select("select a.* from order_detail as a LEFT JOIN order_apply as b on a.order_id = b.id where b.`status` <> 2 and a.deleted=0 and a.residue_course_count > 0 and a.student_id = #{studentId} and a.course_id = #{courseId} and a.school_id=#{schoolId}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "orderCoursePricingModel",
                    column = "id",
                    one = @One(select = "com.xmy.cultivate.mapper.OrderCoursePricingMapper.selectById")
            )
    })
    public abstract List<OrderDetail> getLessonByCourseId(@Param("courseId") Long courseId,@Param("studentId") Long studentId,@Param("schoolId") Long schoolId);


    /**
     * 获取报读校区
     * @param studentId
     * @return
     */
    @Select("select b.id,b.name from order_detail as a LEFT JOIN organization as b on a.school_id = b.id where a.student_id = #{studentId} and a.status <> 2")
    public abstract List<Organization> getSignUpSchoolList(@Param("studentId") Long studentId);

    /**
     * 获取报读校区
     * @param
     * @return
     */
    @Select("select b.id,b.name from order_detail as a LEFT JOIN organization as b on a.school_id = b.id ${ew.customSqlSegment}")
    public abstract List<Organization> getSignUpSchoolListForWhere(@Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 获取对应课程最早的报读信息，记上课，扣课时用
     * @param gradeId 班级id
     * @param count 此次扣多少个课时
     * @return
     */
    @Select("select a.id,a.order_detail_id,a.student_id,a.grade_id,a.use_course_count,a.year_part, " +
            "b.residue_course_count,b.residue_price,b.all_course_count,b.serve_price,b.materials_price " +
            "from student_grade as a LEFT JOIN order_detail as b on a.order_detail_id = b.id " +
            "where a.deleted = 0 and a.`status` = 1 and a.reading_status = 1 and b.reading_status = 1 and b.`status`<>2 and b.residue_course_count >= #{count} and a.student_id = #{studentId} and a.grade_id=#{gradeId}  " +
            "ORDER BY b.id DESC limit 0,1")
    public abstract FirstSignUp getFirstSignUp(@Param("studentId") Long studentId,@Param("gradeId") Long gradeId,@Param("count") Integer count);

    @Select("select sum(use_course_count) as use_course_count from order_detail where order_id = #{orderId}")
    public abstract Integer getUseHouseForOrderId(@Param("orderId") Long orderId);

    @Select("select * from income_day_report_view ${ew.customSqlSegment}")
    public abstract IPage<IncomeDayReport> getIncomeDayReport(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 交费率使用
     * 获取课时人数
     * @return
     */
    @Select("select count(student_id) from (select * from (select sum(residue_course_count) as residue_course_count,school_id,student_id,subjects_id from order_detail ${ew.customSqlSegment}) as t where 1=1 ${where02}) as tc")
    public abstract Integer getCourseCount(@Param(Constants.WRAPPER) Wrapper wrapper,@Param("where02") String where02);

    /**
     * 获取缴费学生详情
     * @param wrapper
     * @param where02
     * @return
     */
    @Select("SELECT *,(select name from student where id = student_id) as student_name FROM (SELECT sum( residue_course_count ) AS residue_course_count," +
            " id, " +
            " school_id, " +
            "student_id, " +
            "subjects_id " +
            "FROM " +
            " order_detail ${ew.customSqlSegment}) AS t where 1=1 ${where02}")
    public abstract IPage<StudentRatePay> getCourseCountStudent(IPage<?> page,@Param(Constants.WRAPPER) Wrapper wrapper, @Param("where02") String where02);



    @Select("select IFNULL(sum(num),0) as all_num from (select sum(num) as num from rate_pay as t ${ew.customSqlSegment}) as t2")
    public abstract Integer getRatePayCount(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select *,(select name from student where id = student_id) as student_name,(select name from grade where id = grade_id) as grade_name,getStaffNameForId(teacher_id) as teacher_name from rate_pay as t ${ew.customSqlSegment}")
    public abstract IPage<StudentRatePay> getRatePayCountDetail(IPage<?> page,@Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select sum(residue_course_count) from order_detail ${ew.customSqlSegment}")
    public abstract Integer selectResidueCourseCount(@Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 交费率使用
     * 获取课时人数
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "count( t3.id ) AS studentAllCount,",
            "SUM( CASE WHEN residue_course_count_all &gt;= ${feeCourseNum} THEN 1 ELSE 0 END ) AS bigFifteen,",
            "SUM( CASE WHEN residue_course_count_all  &lt; ${feeCourseNum} THEN 1 ELSE 0 END ) AS littleFifteen,",
            "teacher_id,",
            "teacherName,",
            "t3.school_id,",
            "getSchoolName(t3.school_id) as schoolName,",
            "subjects_id,",
            "getSubjectsName(subjects_id) as subjects_name,",
            "year_class_id, ",
            "(",
            "SELECT",
            "IFNULL( sum( CASE WHEN num &gt;= ${periodsNum} THEN 1 ELSE 0 END ), 0 ) AS all_num",
            "FROM",
            "( SELECT sum( t01.num ) AS num FROM rate_pay_view as t01",
            "LEFT JOIN rade_exclude_student as t07 on t01.student_id = t07.student_id and t07.classify_id = 1 and t07.deleted=0 and t07.type =1 and t01.year_part = t07.year_part and t01.quarter_num = t07.quarter_num and (FIND_IN_SET(t01.subjects_id,t07.subjects_ids) or t07.subjects_ids is null) and (FIND_IN_SET(t01.school_id,t07.school_ids) or t07.school_ids is null)",
            "WHERE",
            "t01.deleted =0 and t01.status=1",
            "and t07.id is null",
            "and t01.year_part = ${payYearPart}",
            "AND t01.quarter_num = ${payQuarterNum}",
            "and t01.lesson_type = t3.lesson_type",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t01.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t01.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos;'>",
            "and t01.add_date &gt;= #{startDate}",
            "</when>",
            "<when test='endDate!=null and endDate!=&apos;&apos;'>",
            "and t01.add_date &lt;= #{endDate}",
            "</when>",
            "<when test='type != 7'>",
                "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
                    "<choose>",
                        "<when test='quarterNum == 2'>",
                            "and t01.year_class_id &lt; 10 ",
                        "</when>",
                        "<otherwise>",
                            "and t01.year_class_id &lt; 10 ",
                        "</otherwise>",
                    "</choose>",
                "</when>",
            "</when>",

            "<when test='type == 1 or type == 3 '>",
            "and t01.teacher_id = t3.teacher_id",
            "</when>",
            "<when test='type == 2'>",
            "and school_id = t3.school_id",
            "and t01.teacher_id = t3.teacher_id",
            "</when>",
            "<when test='type == 4 or type == 6'>",
            "and t01.school_id = t3.school_id",
            "</when>",
            "<when test='type == 7'>",
            "AND t01.year_class_id = t3.year_class_id ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t01.subjects_id = t3.subjects_id",
            "</when>",
            "GROUP BY",
            "t01.teacher_id,",
            "t01.student_id,",
            "t01.subjects_id ",
            ") AS t_rate_pay",
            ")",
            " AS payNum",
            "<if test= 'type == 6'>",
            ",t01.pid as schoolPid",
            "FROM ",
            "( SELECT * FROM school_quarter WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND deleted = 0) AS t01 ",
            "LEFT JOIN (",
            "</if>",
            "<if test= 'type != 6'>",
            "FROM ( ",
            "</if>",
            "SELECT",
            "id,",
            "school_id,",
            "teacher_id,",
            "student_id,",
            "subjects_id,",
            "year_part,",
            "quarter_num,",
            "grade_id,",
            "lesson_type,",
            "year_class_id,",
            "course_type_id,",
            "residue_course_count_all,",
            "teacherName",
            "FROM",
            "(",
            "SELECT",
                    "id,",
                    "school_id,",
                    "teacher_id,",
                    "student_id,",
                    "subjects_id,",
                    "year_part,",
                    "quarter_num,",
                    "grade_id,",
                    "lesson_type,",
                    "year_class_id,",
                    "course_type_id,",
                    "teacherName,",
                    "sum( residue_course_count_all ) AS residue_course_count_all",
                    "FROM(",
            "SELECT",
            "t1.*,sum( IFNULL(t2.residue_course_count,0)) AS residue_course_count_all,sff.`name` AS teacherName",
            "FROM",
            "student_grade_view AS t1",
            "INNER JOIN staff AS sff ON sff.id = t1.teacher_id",
            "LEFT JOIN",
            "<when test='isHistory == 0'>",
            "order_detail AS t2",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history AS t2",
            "</when>",
            "ON t2.student_id = t1.student_id",
            "AND t2.school_id = t1.school_id",
            "AND t2.subjects_id = t1.subjects_id",
            "AND t2.year_part = t1.year_part",
            "AND t2.lesson_type = t1.lesson_type",
            "AND t2.quarter_num = t1.quarter_num",
            "AND t2.STATUS &lt;&gt; 2",
            "AND t2.deleted = 0",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE",
            "t7.id is null",
            "and t1.year_part = ${yearPart}",
            "AND t1.quarter_num = ${quarterNum}",
            "and t1.is_last = 1",
            "and t1.is_makeup = 0",
            "and t1.deleted = 0",
            "and t1.status = 1",
            "and t1.lesson_type = ${lessonType}",
            "and (t1.reading_status in(1,4,5,6) or (t1.reading_status=3 and t1.use_course_count&gt;0))",
            "<when test='type != 7'>",
                "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
                    "<choose>",
                        "<when test='quarterNum == 2'>",
                            "and t1.year_class_id &lt; 10",
                        "</when>",
                        "<otherwise>",
                            "and t1.year_class_id &lt; 10",
                        "</otherwise>",
                    "</choose>",
                "</when>",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t1.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t1.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and t1.subjects_id = #{subjectsId}",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "  and t1.school_id in(${schoolId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and t1.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==4'>",
            "  and t1.school_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='type ==3'>",
            "  and t1.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</when>",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff.`name` LIKE '%${name}%'",
            "</when>",
            "GROUP BY",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.teacher_id",
            "UNION ALL",
            "SELECT",
            "t1.*,sum(IFNULL(t6.all_course_count,0)) AS residue_course_count_all,sff.`name` AS teacherName",
            "FROM",
            "student_grade_view AS t1",
            "INNER JOIN staff AS sff ON sff.id = t1.teacher_id",
            "LEFT JOIN order_detail AS t6 ON t6.student_id = t1.student_id",
            "AND t6.subjects_id = t1.subjects_id ",
            "AND t6.lesson_type = t1.lesson_type ",
            "AND t6.year_and_quarter > ${nowYearAndQuarter} ",
            "AND t6.year_and_quarter &lt;= ${nextYearAndQuarter} ",
            "AND t6.STATUS &lt;&gt; 2 ",
            "AND t6.deleted = 0 ",
            "AND t6.order_type = 1",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE",
            "t7.id is null",
            "and t1.year_part = ${yearPart}",
            "AND t1.quarter_num = ${quarterNum}",
            "and t1.is_last = 1",
            "and t1.is_makeup = 0",
            "and t1.deleted = 0",
            "and t1.status = 1",
            "and t1.lesson_type = ${lessonType}",
            "and (t1.reading_status in(1,4,5,6) or (t1.reading_status=3 and t1.use_course_count&gt;0))",
            "<when test='type != 7'>",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "and t1.year_class_id &lt; 10",
            "</when>",
            "<otherwise>",
            "and t1.year_class_id &lt; 10",
            "</otherwise>",
            "</choose>",
            "</when>",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t1.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t1.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and t1.subjects_id = #{subjectsId}",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "  and t1.school_id in(${schoolId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and t1.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==4'>",
            "  and t1.school_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='type ==3'>",
            "  and t1.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</when>",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff.`name` LIKE '%${name}%'",
            "</when>",
            "GROUP BY",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.teacher_id",
            ") AS t01 ",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "teacher_id ",
            ") AS t",
            ") AS t3",
            "<if test= 'type == 6'>",
            "ON t3.school_id = t01.school_id ",
            "</if>",
            "where 1=1",
            "<if test= 'type == 6'>",
            "and t3.school_id is not null",
            "</if>",
            "<when test='type == 1 or type == 3'>",
            "GROUP BY t3.teacher_id",
            "</when>",
            "<when test='type == 2'>",
            "GROUP BY t3.school_id,t3.teacher_id",
            "</when>",
            "<when test='type == 4 or type == 6'>",
            "GROUP BY t3.school_id",
            "</when>",
            "<when test='type == 7'>",
            "GROUP BY year_class_id ",
            "ORDER BY year_class_id ",
            "</when>",
            "</script>"})
    public abstract List<RenewClassRecord> getPayFee(@Param("schoolId") String schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("isHistory") Integer isHistory,@Param("lessonType") Integer lessonType,@Param("nowYearAndQuarter") Integer nowYearAndQuarter,@Param("nextYearAndQuarter") Integer nextYearAndQuarter,@Param("payYearPart") Integer payYearPart,@Param("payQuarterNum") Integer payQuarterNum,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("feeCourseNum") Integer feeCourseNum,@Param("periodsNum") Integer periodsNum,@Param("name") String name);

    /**
     * 交费率使用
     * 获取课时人数
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "count( t1.id ) AS studentAllCount,",
            "SUM( CASE WHEN residue_course_count &gt;= ${feeCourseNum} THEN 1 ELSE 0 END ) AS bigFifteen,",
            "SUM( CASE WHEN residue_course_count  &lt; ${feeCourseNum} THEN 1 ELSE 0 END ) AS littleFifteen,",
            "teacher_id,",
            "teacherName,",
            "t1.school_id,",
            "getSchoolName(t1.school_id) as schoolName,",
            "subjects_id,",
            "getSubjectsName(subjects_id) as subjects_name, ",
            "year_class_id,  ",
            "(",
            "SELECT",
            "IFNULL( sum( CASE WHEN num &gt;= ${periodsNum} THEN 1 ELSE 0 END ), 0 ) AS all_num",
            "FROM",
            "( SELECT sum( t01.num ) AS num FROM rate_pay_view as t01",
            "LEFT JOIN rade_exclude_student as t07 on t07.classify_id = 1 and t07.deleted = 1 and t07.type =1 and t01.student_id = t07.student_id and t01.year_part = t07.year_part and t01.quarter_num = t07.quarter_num and (FIND_IN_SET(t01.subjects_id,t07.subjects_ids) or t07.subjects_ids is null) and (FIND_IN_SET(t01.school_id,t07.school_ids) or t07.school_ids is null)",
            "WHERE",
            "t01.deleted =0 and t01.status=1",
            "and t07.id is null",
            "and t01.year_part = ${yearPart} ",
            "AND t01.quarter_num = ${quarterNum} ",
            "and t01.lesson_type = ${lessonType} ",
            "<when test='type == 7'>",
                "AND t01.year_class_id = t1.year_class_id ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t01.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t01.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos;'>",
            "and t01.add_date &gt;= #{startDate}",
            "</when>",
            "<when test='endDate!=null and endDate!=&apos;&apos;'>",
            "and t01.add_date &lt;= #{endDate}",
            "</when>",
            "<when test='type != 7'>",
                "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
                    "<choose>",
                        "<when test='quarterNum == 2'>",
                            "and t01.year_class_id &lt; 10",
                        "</when>",
                        "<otherwise>",
                            "and t01.year_class_id &lt; 10",
                        "</otherwise>",
                    "</choose>",
                "</when>",
            "</when>",
            "<when test='type == 1 or type == 3'>",
            "and t01.teacher_id = t1.teacher_id",
            "</when>",
            "<when test='type == 2'>",
            "and t01.school_id = t1.school_id",
            "and t01.teacher_id = t1.teacher_id",
            "</when>",
            "<when test='type == 4 or type == 6'>",
            "and t01.school_id = t1.school_id",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t01.subjects_id = t1.subjects_id",
            "</when>",
            "GROUP BY",
            "t01.teacher_id,",
            "t01.student_id,",
            "t01.subjects_id ",
            ") AS t_rate_pay",
            ")",
            " AS payNum",

            "<if test= 'type == 6'>",
            ",t01.pid as schoolPid",
            "FROM ",
            "( SELECT * FROM school_quarter WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND deleted = 0) AS t01 ",
            "LEFT JOIN (",
            "</if>",
            "<if test= 'type != 6'>",
            "FROM ( ",
            "</if>",

            "SELECT ",
            "t1.*,t2.year_class_id,sff.`name` AS teacherName",
            "FROM ",
            "`student_residue_course` AS t1 ",
            "INNER JOIN (select id,`name` from staff) AS sff ON sff.id = t1.teacher_id",
            "LEFT JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.lesson_type = ${lessonType} ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND t1.subjects_id = ${subjectsId} ",
            "</when>",
            "<when test='type ==3'>",
            "AND t1.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242) ",
            "</when>",
            "AND '${endDate}' BETWEEN t1.start_date and t1.end_date",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t2.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='type != 7'>",
                "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
                    "<choose>",
                        "<when test='quarterNum == 2'>",
                            "AND t2.year_class_id &lt; 10",
                        "</when>",
                        "<otherwise>",
                            "AND t2.year_class_id &lt; 10",
                        "</otherwise>",
                    "</choose>",
                "</when>",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "AND t1.school_id in(${schoolId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  AND t2.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  AND t1.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==4'>",
            " and t1.school_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "and t7.id is null",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff.`name` LIKE '%${name}%'",
            "</when>",
            ") AS t1",
            "<if test= 'type == 6'>",
            "ON t1.school_id = t01.school_id ",
            "</if>",
            "where 1=1",
            "<if test= 'type == 6'>",
            "and t1.school_id is not null",
            "</if>",
            "<when test='type == 1 or type == 3'>",
            "GROUP BY teacher_id ",
            "</when>",
            "<when test='type == 2'>",
            "GROUP BY t1.school_id,teacher_id ",
            "</when>",
            "<when test='type == 4 or type == 6'>",
            "GROUP BY t1.school_id ",
            "</when>",
            "<when test='type == 7'>",
            "GROUP BY year_class_id",
            "ORDER BY year_class_id",
            "</when>",
            "</script>"})
    public abstract List<RenewClassRecord> getPayFeeByHistory(@Param("schoolId") String schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("lessonType") Integer lessonType,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("feeCourseNum") Integer feeCourseNum,@Param("periodsNum") Integer periodsNum,@Param("name") String name);

    @Select({"<script>",
            "SELECT",
            "school_id,schoolName,subjects_id,teacher_id,teacherName,market_teacher_id,marketTeacherName,team_name,team_id,team_pid,subjects_name,",
            "sum( studentAllCount ) AS studentAllCount,",
            "sum( bigFifteen ) AS payNum ,",
            "sum( bigFifteen ) AS bigFifteen, ",
            "sum( littleFifteen ) AS littleFifteen ",
            "FROM ",
            "( ",
            "SELECT ",
            "count( id ) AS studentAllCount,",
            "SUM( CASE WHEN residue_course_count &gt;= ${feeCourseNum} THEN 1 ELSE 0 END ) AS bigFifteen, ",
            "SUM( CASE WHEN residue_course_count &lt; ${feeCourseNum} THEN 1 ELSE 0 END ) AS littleFifteen,",
            "school_id,",
            "getSchoolName ( school_id ) AS schoolName, ",
            "subjects_id,",
            "teacher_id, ",
            "getStaffNameForId ( teacher_id ) AS teacherName, ",
            "market_teacher_id, ",
            "getStaffNameForId ( market_teacher_id ) AS marketTeacherName,",
            "getSubjectsName ( subjects_id ) AS subjects_name,",
            "team_name, ",
            "team_id, ",
            "team_pid ",
            "FROM ",
            "( ",
            "SELECT ",
            "t01.id,",
            "t01.school_id, ",
            "t01.teacher_id, ",
            "t01.student_id, ",
            "t01.subjects_id, ",
            "t01.year_part, ",
            "t01.year_class_id, ",
            "t01.course_type_id, ",
            "t01.quarter_num, ",
            "t01.grade_id, ",
            "t01.lesson_type, ",
            "t01.residue_course_count, ",
            "t02.teacher_id AS market_teacher_id, ",
            "t02.team_name, ",
            "t02.team_id, ",
            "t02.team_pid ",
            "FROM ",
            "( ",
            "SELECT ",
            "src.*,g.year_class_id,g.course_type_id ",
            "FROM ",
            "student_residue_course AS src ",
            "LEFT JOIN grade AS g ON src.grade_id = g.id ",
            "WHERE ",
            "src.lesson_type = ${lessonType} ",
            "AND src.reading_status = 1 ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND src.subjects_id = ${subjectsId} ",
            "</when>",
            "AND src.start_date &gt;= '${startDate}' ",
            "AND src.end_date &lt;= '${endDate}' ",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            " and g.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "AND g.year_class_id &lt; 13",
            "</when>",
            "<otherwise>",
            "AND g.year_class_id &lt; 13",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "AND g.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "AND src.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='type ==3'>",
            "  and src.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</when>",
            "GROUP BY",
            "src.school_id,src.student_id,src.subjects_id,src.teacher_id ",
            ") AS t01 ",
            "INNER JOIN ( ",
            "SELECT ",
            "td.*, ",
            "tm.teacher_id,tm.team_name,tm.id AS team_id,tm.pid AS team_pid ",
            "FROM ",
            "track_detail AS td ",
            "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE ",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType} ",
            "AND tm.`status` = 1 ",
            "AND tm.deleted = 0 ",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
            "AND tm.id in (${teamId}) ",
            "</when>",
            "GROUP BY ",
            "td.track_id,td.sourse_id ",
            ") AS t02 ON t01.teacher_id = t02.sourse_id ",
            "AND ( find_in_set( t01.year_class_id, t02.year_class_id_str ) OR t02.year_class_id_str = '' ) ",
            "AND ( find_in_set( t01.school_id, t02.school_id_str ) OR t02.school_id_str = '' ) ",
            "AND ( find_in_set( t01.course_type_id, t02.class_type_id_str ) OR t02.class_type_id_str = '' ) ",
            ") AS t1 ",
            "GROUP BY ",
            "team_id,teacher_id",
            ") AS t ",
            "<when test='teamId==null or teamId==&apos;&apos; '>",
            "GROUP BY team_id ",
            "</when>",
            "</script>"})
    public abstract List<RenewClassRecord> getPayFeeMarketByDate(@Param("schoolId") String schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("lessonType") Integer lessonType,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("feeCourseNum") Integer feeCourseNum,@Param("marketTeacherId") String marketTeacherId,@Param("trackType") Integer trackType,@Param("classify") Integer classify,@Param("teamId")String teamId);



    /**
     * 市场部交费率使用
     * 获取课时人数
     * @return
     */
    @Select({"<script>",
            "select",
            "school_id,",
            "schoolName,",
            "subjects_id,",
            "teacher_id,",
            "teacherName,",
            "market_teacher_id,",
            "marketTeacherName,",
            "team_name,",
            "team_id,",
            "team_pid,",
            "subjects_name",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
            ",studentAllCount,",
            "bigFifteen,",
            "littleFifteen,",
            "payNum",
            "</when>",
            "<when test='teamId==null or teamId==&apos;&apos; '>",
            ",sum(studentAllCount) as studentAllCount,",
            "sum(bigFifteen) as bigFifteen,",
            "sum(littleFifteen) as littleFifteen,",
            "sum(payNum) as payNum",
            "</when>",
            "from (",
            "SELECT",
            "count( id ) AS studentAllCount,",
            "SUM( CASE WHEN residue_course_count_all &gt;= ${feeCourseNum} THEN 1 ELSE 0 END ) AS bigFifteen,",
            "SUM( CASE WHEN residue_course_count_all  &lt; ${feeCourseNum} THEN 1 ELSE 0 END ) AS littleFifteen,",
            "school_id,",
            "getSchoolName(school_id) as schoolName,",
            "subjects_id,",
            "teacher_id,",
            "getStaffNameForId ( teacher_id ) AS teacherName,",
            "market_teacher_id,",
            "getStaffNameForId ( market_teacher_id ) AS marketTeacherName,",
            "getSubjectsName(subjects_id) as subjects_name,",
            "team_name,",
            "team_id,",
            "team_pid,",
            "(",
            "SELECT",
            "IFNULL( sum( CASE WHEN num &gt;= ${periodsNum} THEN 1 ELSE 0 END ), 0 ) AS all_num",
            "FROM",
            "( SELECT sum( num ) AS num FROM rate_pay_view AS rp",
            "INNER JOIN (",
            "SELECT",
            "td.*,",
            "tm.teacher_id",
            "FROM",
            "track_detail AS td",
            "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE",
            "tm.classify = ${classify}",
            "AND tm.track_type = ${trackType} ",
            "AND tm.status = 1 ",
            "AND tm.deleted = 0 ",
            "and td.track_id = t3.team_id",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
            "  and tm.id in (${teamId})",
            "</when>",
            "GROUP BY",
            "td.track_id,",
            "td.sourse_id ",
            ") AS ray_t02 ON rp.teacher_id = ray_t02.sourse_id ",
            "AND ( find_in_set( rp.year_class_id, ray_t02.year_class_id_str ) OR ray_t02.year_class_id_str = '' ) ",
            "AND ( find_in_set( rp.school_id, ray_t02.school_id_str ) OR ray_t02.school_id_str = '' ) ",
            "AND ( find_in_set( rp.course_type_id, ray_t02.class_type_id_str ) OR ray_t02.class_type_id_str = '' ) ",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and rp.student_id = t7.student_id and rp.year_part = t7.year_part and rp.quarter_num = t7.quarter_num and (FIND_IN_SET(rp.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(rp.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE",
            "rp.deleted =0 and rp.status=1",
            "and t7.id is null",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and rp.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and rp.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and rp.subjects_id = #{subjectsId}",
            "</when>",
            "and rp.year_part = ${payYearPart}",
            "AND rp.quarter_num = ${payQuarterNum}",
            "and rp.lesson_type = t3.lesson_type",
            "<when test='startDate!=null and startDate!=&apos;&apos;'>",
            "and rp.add_date &gt;= #{startDate}",
            "</when>",
            "<when test='endDate!=null and endDate!=&apos;&apos;'>",
            "and rp.add_date &lt;= #{endDate}",
            "</when>",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and rp.year_class_id &lt; 10",
//            "  and rp.year_class_id &lt; 13",
            //"  and rp.year_class_id &lt;&gt; 6",
            "</when>",
            "<otherwise>",
            "  and rp.year_class_id &lt; 10",
//            "  and rp.year_class_id &lt; 13",
            //"  and rp.year_class_id &lt;&gt; 6",
            "</otherwise>",
            "</choose>",
            "</when>",
            "and rp.teacher_id = t3.teacher_id",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and rp.subjects_id = t3.subjects_id",
            "</when>",
            "GROUP BY",
            "rp.student_id,",
            "rp.subjects_id ",
            ") AS t_rate_pay",
            ")",
            " AS payNum",
            "FROM",
            "(",
            "SELECT",
            "t.id,",
            "t.school_id,",
            "t.teacher_id,",
            "t.student_id,",
            "t.subjects_id,",
            "t.year_part,",
            "t.year_class_id,",
            "t.course_type_id,",
            "t.quarter_num,",
            "t.grade_id,",
            "t.lesson_type,",
            "t.residue_course_count_all,",
            "t02.teacher_id AS market_teacher_id,",
            "t02.team_name,",
            "t02.team_id,",
            "t02.team_pid",
            "FROM",
            "(",
            "SELECT",
            "t1.*,sum( IFNULL(t2.residue_course_count,0) + IFNULL(t6.all_course_count,0)) AS residue_course_count_all",
            "FROM",
            "student_grade_view AS t1",
            "LEFT JOIN",
            "<when test='isHistory == 0'>",
            "order_detail AS t2",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history AS t2",
            "</when>",
            "ON t2.student_id = t1.student_id",
            "AND t2.school_id = t1.school_id",
            "AND t2.subjects_id = t1.subjects_id",
            "AND t2.year_part = t1.year_part",
            "AND t2.lesson_type = t1.lesson_type",
            "AND t2.quarter_num = t1.quarter_num",
            "AND t2.STATUS &lt;&gt; 2",
            "AND t2.deleted = 0",
            "LEFT JOIN order_detail AS t6 ON t6.student_id = t1.student_id",
            "AND t6.subjects_id = t1.subjects_id ",
            "AND t6.lesson_type = t1.lesson_type ",
            "AND t6.year_and_quarter > ${nowYearAndQuarter} ",
            "AND t6.year_and_quarter &lt;= ${nextYearAndQuarter} ",
            "AND t6.STATUS &lt;&gt; 2 ",
            "AND t6.deleted = 0 ",
            "AND t6.order_type = 1",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE",
            "t1.year_part = ${yearPart}",
            "and t7.id is null",
            "AND t1.quarter_num = ${quarterNum}",
            "and t1.is_last = 1",
            "and t1.is_makeup = 0",
            "and t1.deleted = 0",
            "and t1.status = 1",
            "and t1.lesson_type = ${lessonType}",
            "and (t1.reading_status in(1,4,5,6) or (t1.reading_status=3 and t1.use_course_count&gt;0))",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and t1.year_class_id &lt; 10",
//            "  and t1.year_class_id &lt; 13",
            //"  and t1.year_class_id &lt;&gt; 6",
            "</when>",
            "<otherwise>",
            "  and t1.year_class_id &lt; 10",
//            "  and t1.year_class_id &lt; 13",
            //"  and t1.year_class_id &lt;&gt; 6",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t1.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t1.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and t1.subjects_id = #{subjectsId}",
            "</when>",
            "GROUP BY",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.teacher_id",
            ") AS t",
            "INNER JOIN (",
            "SELECT",
            "td.*,",
            "tm.teacher_id,",
            "tm.team_name,",
            "tm.id AS team_id,",
            "tm.pid AS team_pid",
            "FROM",
            "track_detail AS td",
            "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE",
            "tm.classify = ${classify} ",
            "and tm.track_type = ${trackType} ",
            "AND tm.status = 1 ",
            "AND tm.deleted =0 ",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
            "  and tm.id in (${teamId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and tm.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "GROUP BY",
            "td.track_id,",
            "td.sourse_id ",
            ") AS t02 ON t.teacher_id = t02.sourse_id ",
            "AND ( find_in_set( t.year_class_id, t02.year_class_id_str ) OR t02.year_class_id_str = '' ) ",
            "AND ( find_in_set( t.school_id, t02.school_id_str ) OR t02.school_id_str = '' ) ",
            "AND ( find_in_set( t.course_type_id, t02.class_type_id_str ) OR t02.class_type_id_str = '' )",
            ") AS t3 ",
            "GROUP BY team_id,teacher_id",
            ") as t001",
            "<when test='teamId==null or teamId==&apos;&apos; '>",
            "GROUP BY team_id",
            "</when>",
            "</script>"})
    public abstract List<RenewClassRecord> getPayFeeMarket(@Param("schoolId") String schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("isHistory") Integer isHistory,@Param("lessonType") Integer lessonType,@Param("nowYearAndQuarter") Integer nowYearAndQuarter,@Param("nextYearAndQuarter") Integer nextYearAndQuarter,@Param("payYearPart") Integer payYearPart,@Param("payQuarterNum") Integer payQuarterNum,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("feeCourseNum") Integer feeCourseNum,@Param("marketTeacherId") String marketTeacherId,@Param("trackType") Integer trackType,@Param("classify") Integer classify,@Param("teamId")String teamId,@Param("periodsNum") Integer periodsNum);



    @Select({"<script>",
            "SELECT ",
            "count( id ) AS studentAllCount, ",
            "SUM( CASE WHEN residue_course_count_all &gt;= ${feeCourseNum} THEN 1 ELSE 0 END ) AS bigFifteen, ",
            "SUM( CASE WHEN residue_course_count_all &lt; ${feeCourseNum} THEN 1 ELSE 0 END ) AS littleFifteen, ",
            "teacher_id, ",
            "getStaffNameForId ( teacher_id ) AS teacherName, ",
            "school_id, ",
            "getSchoolName ( school_id ) AS schoolName, ",
            "subjects_id, ",
            "getSubjectsName ( subjects_id ) AS subjects_name, ",
            "year_part, ",
            "`quarter_num`, ",
            "course_type_id as class_type_id,",
            "getCourseTypeName(course_type_id) as class_type_name,",
            "year_class_id",
            "FROM ",
            "( ",
            "SELECT ",
            "t1.id, ",
            "t1.school_id, ",
            "t1.teacher_id, ",
            "t1.student_id, ",
            "t1.subjects_id, ",
            "t1.year_part, ",
            "t1.quarter_num, ",
            "t1.grade_id, ",
            "t1.lesson_type, ",
            "t1.course_type_id, ",
            "t1.year_class_id,",
            "sum( ",
            "IFNULL( t2.residue_course_count, 0 )) AS residue_course_count_all  ",
            "FROM ",
            "student_grade_view AS t1 ",
            "LEFT JOIN order_detail_history AS t2 ON t2.student_id = t1.student_id  ",
            "AND t2.school_id = t1.school_id  ",
            "AND t2.subjects_id = t1.subjects_id  ",
            "AND t2.year_part = t1.year_part  ",
            "AND t2.lesson_type = t1.lesson_type  ",
            "AND t2.quarter_num = t1.quarter_num  ",
            "AND t2.STATUS &lt;&gt; 2  ",
            "AND t2.deleted = 0  ",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE ",
            "t1.is_last = 1  ",
            "t7.id is null",
            "<when test='yearAndQuarterListBefore != null and yearAndQuarterListBefore.size() > 0 '> ",
            "AND (",
            "<foreach collection='yearAndQuarterListBefore' item='item' open='(' separator='or' close=')'>",
            "t1.`year_part` = ${item.yearPart}",
            "and t1.quarter_num =${item.quarterNum}",
            "</foreach>",
            ")",
            "</when> ",
            "<when test ='schoolId != null and schoolId.length > 0'>",
            "and t1.school_id in",
            "<foreach collection='schoolId' item='item' open='(' separator=',' close=')'>",
            "${item}",
            "</foreach>",
            "</when>",
            "<when test ='yearClassId != null and yearClassId.length > 0'>",
            "and t1.year_class_id in",
            "<foreach collection='yearClassId' item='item' open='(' separator=',' close=')'>",
            "${item}",
            "</foreach>",
            "</when>",
            "<when test ='classTypeId != null and classTypeId.length > 0'>",
            "and t1.course_type_id in",
            "<foreach collection='classTypeId' item='item' open='(' separator=',' close=')'>",
            "${item}",
            "</foreach>",
            "</when>",
            "<when test ='subjectsId != null and subjectsId.length > 0'>",
            "and t1.subjects_id in",
            "<foreach collection='subjectsId' item='item' open='(' separator=',' close=')'>",
            "${item}",
            "</foreach>",
            "</when>",
            "<when test ='teacherId != null and teacherId.length > 0'>",
            "and t1.teacher_id in",
            "<foreach collection='teacherId' item='item' open='(' separator=',' close=')'>",
            "${item}",
            "</foreach>",
            "</when>",
            "AND t1.is_makeup = 0  ",
            "AND t1.deleted = 0  ",
            "AND t1.STATUS = 1  ",
            "AND t1.lesson_type = ${lessonType}  ",
            "AND ( ",
            "t1.reading_status IN ( 1, 4, 5, 6 )  ",
            "OR ( t1.reading_status = 3 AND t1.use_course_count &gt; 0 ))  ",
            "<when test ='yearClassId == null or (yearClassId != null and yearClassId.length == 0)'>",
            "AND t1.year_class_id &lt; 13  ",
            //"  and t1.year_class_id &lt;&gt; 6",
            "</when>",
            "GROUP BY ",
            "t1.year_part, ",
            "t1.quarter_num, ",
            "t1.school_id, ",
            "t1.student_id, ",
            "t1.subjects_id, ",
            "t1.teacher_id UNION ALL ",
            "SELECT ",
            "t1.id, ",
            "t1.school_id, ",
            "t1.teacher_id, ",
            "t1.student_id, ",
            "t1.subjects_id, ",
            "t1.year_part, ",
            "t1.quarter_num, ",
            "t1.grade_id, ",
            "t1.lesson_type, ",
            "t1.course_type_id, ",
            "t1.year_class_id,",
            "sum( ",
            "IFNULL( t2.residue_course_count, 0 )) AS residue_course_count_all  ",
            "FROM ",
            "student_grade_view AS t1 ",
            "LEFT JOIN order_detail AS t2 ON t2.student_id = t1.student_id  ",
            "AND t2.school_id = t1.school_id  ",
            "AND t2.subjects_id = t1.subjects_id  ",
            "AND t2.year_part = t1.year_part  ",
            "AND t2.lesson_type = t1.lesson_type  ",
            "AND t2.quarter_num = t1.quarter_num  ",
            "AND t2.STATUS &lt;&gt; 2  ",
            "AND t2.deleted = 0  ",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE ",
            "t1.deleted = 0  ",
            "and t7.id is null",
            "<when test='yearAndQuarterListAfter != null and yearAndQuarterListAfter.size() > 0'> ",
            "AND (",
            "<foreach collection='yearAndQuarterListAfter' item='item' open='(' separator='or' close=')'>",
            "t1.`year_part` = ${item.yearPart}",
            "and t1.quarter_num =${item.quarterNum}",
            "</foreach>",
            ")",
            "</when> ",
            "<when test='yearAndQuarterListAfter != null and yearAndQuarterListAfter.size() == 0'> ",
            "and t1.`year_part` = 2000",
            "and t1.quarter_num = 0",
            "</when> ",
            "<when test ='schoolId != null and schoolId.length > 0'>",
            "and t1.school_id in",
            "<foreach collection='schoolId' item='item' open='(' separator=',' close=')'>",
            "${item}",
            "</foreach>",
            "</when>",
            "<when test ='yearClassId != null and yearClassId.length > 0'>",
            "and t1.year_class_id in",
            "<foreach collection='yearClassId' item='item' open='(' separator=',' close=')'>",
            "${item}",
            "</foreach>",
            "</when>",
            "<when test ='classTypeId != null and classTypeId.length > 0'>",
            "and t1.course_type_id in",
            "<foreach collection='classTypeId' item='item' open='(' separator=',' close=')'>",
            "${item}",
            "</foreach>",
            "</when>",
            "<when test ='subjectsId != null and subjectsId.length > 0'>",
            "and t1.subjects_id in",
            "<foreach collection='subjectsId' item='item' open='(' separator=',' close=')'>",
            "${item}",
            "</foreach>",
            "</when>",
            "<when test ='teacherId != null and teacherId.length > 0'>",
            "and t1.teacher_id in",
            "<foreach collection='teacherId' item='item' open='(' separator=',' close=')'>",
            "${item}",
            "</foreach>",
            "</when>",
            "AND t1.is_last = 1  ",
            "AND t1.is_makeup = 0  ",
            "AND t1.STATUS = 1  ",
            "AND t1.lesson_type = ${lessonType}  ",
            "AND ( ",
            "t1.reading_status IN ( 1, 4, 5, 6 )  ",
            "OR ( t1.reading_status = 3 AND t1.use_course_count &gt; 0 ))  ",
            "<when test ='yearClassId == null or (yearClassId != null and yearClassId.length == 0)'>",
            "AND t1.year_class_id &lt; 13  ",
            //"  and t1.year_class_id &lt;&gt; 6",
            "</when>",
            "GROUP BY ",
            "t1.year_part, ",
            "t1.quarter_num, ",
            "t1.school_id, ",
            "t1.student_id, ",
            "t1.subjects_id, ",
            "<when test ='classTypeId != null and classTypeId.length > 0'>",
            "t1.course_type_id, ",
            "</when>",
            "<when test ='yearClassId != null and yearClassId.length > 0'>",
            "t1.year_class_id, ",
            "</when>",
            "t1.teacher_id  ",
            ") AS tt  ",
            "GROUP BY ",
            "year_part, ",
            "quarter_num",
            "<when test='type == 2'>",
            ",school_id",
            "</when>",
            "<when test='type == 3'>",
            ",teacher_id",
            "</when>",
            "<when test ='subjectsId != null and subjectsId.length > 0'>",
            ",subjects_id",
            "</when>",
            "<when test ='classTypeId != null and classTypeId.length > 0'>",
            ",course_type_id",
            "</when>",
            "<when test ='yearClassId != null and yearClassId.length > 0'>",
            ",year_class_id",
            "</when>",
            "ORDER BY ",
            "year_part, ",
            "quarter_num",
            "</script>"})
    public abstract List<RenewClassRecordChart> payFeeChart(@Param("schoolId") Long[] schoolId, @Param("yearAndQuarterListBefore") List<YearAndQuarter> yearAndQuarterListBefore, @Param("yearAndQuarterListAfter") List<YearAndQuarter> yearAndQuarterListAfter, @Param("quarterNum") Integer[] quarterNum, @Param("subjectsId") Integer[] subjectsId, @Param("teacherId") Long[] teacherId, @Param("yearClassId") Integer[] yearClassId, @Param("classTypeId") Long[] classTypeId, @Param("type") String type, @Param("lessonType") Integer lessonType,@Param("feeCourseNum") Integer feeCourseNum);



    /**
     * 交费率详情
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "*",
            "FROM",
            "(",
            "SELECT",
            "id,",
            "school_id,",
            "teacher_id,",
            "student_id,",
            "subjects_id,",
            "year_part,",
            "quarter_num,",
            "grade_id,",
            "residue_course_count_all as residue_course_count,",
            "getStaffNameForId ( teacher_id ) AS teacher_name,",
            "getSchoolName(school_id) as schoolName,",
            "getSubjectsName(subjects_id) as subjects_name,",
            "grade_name,",
            "student_name,",
            "linkman_phone ",
            "FROM",
            "(",
            "SELECT",
            "t1.*,student.linkman_phone, sum( IFNULL(t2.residue_course_count,0) + IFNULL(t6.all_course_count,0)) AS residue_course_count_all",
            "FROM",
            "student_grade_view AS t1",
            "LEFT JOIN",
            "<when test='isHistory == 0'>",
            "order_detail AS t2",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history AS t2",
            "</when>",
            "ON t2.student_id = t1.student_id ",
            "AND t2.school_id = t1.school_id ",
            "AND t2.subjects_id = t1.subjects_id ",
            "AND t2.year_part = t1.year_part ",
            "AND t2.lesson_type = t1.lesson_type ",
            "AND t2.quarter_num = t1.quarter_num ",
            "AND t2.STATUS &lt;&gt; 2 ",
            "AND t2.deleted = 0",
            "LEFT JOIN order_detail AS t6 ON t6.student_id = t1.student_id ",
            "AND t6.subjects_id = t1.subjects_id ",
            "AND t6.lesson_type = t1.lesson_type ",
            "AND t6.year_and_quarter > ${nowYearAndQuarter} ",
            "AND t6.year_and_quarter &lt;= ${nextYearAndQuarter} ",
            "AND t6.STATUS &lt;&gt; 2 ",
            "AND t6.deleted = 0 ",
            "AND t6.order_type = 1",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "LEFT JOIN student ON student.id = t1.student_id ",
            "WHERE",
            "t1.year_part = ${yearPart}",
            "AND t1.quarter_num = ${quarterNum}",
            "and t1.is_last = 1",
            "and t1.is_makeup = 0",
            "and t1.deleted = 0",
            "and t1.status = 1",
            "and t1.lesson_type = ${lessonType}",
            "and (t1.reading_status in(1,4,5,6) or (t1.reading_status=3 and t1.use_course_count&gt;0))",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and t1.subjects_id = #{subjectsId}",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t1.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t1.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and t1.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==4'>",
            "  and t1.school_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='type ==3'>",
            "  and t1.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</when>",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and t1.year_class_id &lt; 10",
//            "  and t1.year_class_id &lt; 13",
            //"  and t1.year_class_id &lt;&gt; 6",
            "</when>",
            "<otherwise>",
            "  and t1.year_class_id &lt; 10",
//            "  and t1.year_class_id &lt; 13",
            //"  and t1.year_class_id &lt;&gt; 6",
            "</otherwise>",
            "</choose>",
            "</when>",
            "and t7.id is null",
            "GROUP BY",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.teacher_id",
            ") AS t",
            ") AS t3 where 1=1",
            "<when test='type == 1 or type == 3'>",
            "and teacher_id=#{teacherId}",
            "</when>",
            "<when test='type == 2'>",
            "and teacher_id=#{teacherId} and school_id = #{schoolId}",
            "</when>",
            "<when test='type == 4'>",
            "and school_id=#{schoolId}",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "and grade_id=#{gradeId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and subjects_id=#{subjectsId}",
            "</when>",
            "<when test='detailType==1'>",
            "and residue_course_count &gt;= ${feeCourseNum}",
            "</when>",
            "<when test='detailType==2'>",
            "and residue_course_count &lt; ${feeCourseNum}",
            "</when>",
            "<when test='detailType==3'>",
            "and teacher_id=0",
            "</when>",
            "</script>"})
    public abstract IPage<StudentRatePay> getPayFeeDetail(IPage<?> page,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") String schoolId,@Param("teacherId") String teacherId,@Param("gradeId") String gradeId,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("detailType") String detailType,@Param("isHistory") Integer isHistory,@Param("lessonType") String lessonType,@Param("nowYearAndQuarter") Integer nowYearAndQuarter,@Param("nextYearAndQuarter") Integer nextYearAndQuarter,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("feeCourseNum") Integer feeCourseNum);


    @Select({"<script>",
            "SELECT t1.*,student.`name` as student_name,t2.`name` AS grade_name,getStaffNameForId ( t1.teacher_id ) AS teacher_name ",
            "FROM `student_residue_course` AS t1 ",
            "LEFT JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "LEFT JOIN student ON student.id = t1.student_id ",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE ",
            "t1.lesson_type = ${lessonType} ",
            "and t7.id is null",
            "AND '${endDate}' BETWEEN t1.start_date and t1.end_date",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t2.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and t2.year_class_id &lt; 10",
//            "  and t2.year_class_id &lt; 13",
            //"  and t2.year_class_id &lt;&gt; 6",
            "</when>",
            "<otherwise>",
            "  and t2.year_class_id &lt; 10",
//            "  and t2.year_class_id &lt; 13",
            //"  and t2.year_class_id &lt;&gt; 6",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and t1.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='type ==3'>",
            "  and t1.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</when>",
            "<when test='type == 1 or type == 3'>",
            "and t1.teacher_id=#{teacherId}",
            "</when>",
            "<when test='type == 2'>",
            "and t1.teacher_id=#{teacherId} and t1.school_id = #{schoolId}",
            "</when>",
            "<when test='type == 4'>",
            "and t1.school_id=#{schoolId}",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "and t1.grade_id=#{gradeId} ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and t1.subjects_id=#{subjectsId}",
            "</when>",
            "<when test='detailType==1 or detailType==3'>",
            "and t1.residue_course_count &gt;= ${feeCourseNum}",
            "</when>",
            "<when test='detailType==2'>",
            "and t1.residue_course_count &lt; ${feeCourseNum}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t2.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            " and t1.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==4'>",
            " and t1.school_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "</script>" })
    public abstract IPage <StudentRatePay> getPayFeeDetailHistory (IPage <?> page,@Param ( "yearPart" ) Integer yearPart,@Param ( "quarterNum" ) Integer quarterNum,@Param ( "schoolId" ) String schoolId,@Param ( "teacherId" ) String teacherId,@Param ( "gradeId" ) String gradeId,@Param ( "subjectsId" ) String subjectsId,@Param ( "type" ) Integer type,@Param ( "startDate" ) String startDate,@Param ( "endDate" ) String endDate,@Param ( "trackId" ) String trackId,@Param ( "detailType" ) String detailType,@Param ( "lessonType" ) String lessonType,@Param ( "yearClassId" ) String yearClassId,@Param ( "classTypeId" ) String classTypeId,@Param ( "feeCourseNum" ) Integer feeCourseNum );

    /**
     * 市场部-交费率详情
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "*",
            "FROM",
            "(",
            "SELECT",
            "t.id,",
            "t.school_id,",
            "t.teacher_id,",
            "t.student_id,",
            "t.subjects_id,",
            "t.year_part,",
            "t.quarter_num,",
            "t.grade_id,",
            "t.residue_course_count_all as residue_course_count,",
            "getStaffNameForId ( t.teacher_id ) AS teacher_name,",
            "getSchoolName(t.school_id) as schoolName,",
            "getSubjectsName(t.subjects_id) as subjects_name,",
            "t.grade_name,",
            "t.student_name,",
            "t02.teacher_id AS market_teacher_id,",
            "t02.team_name,",
            "t02.team_id,",
            "t02.team_pid,",
            "t.linkman_phone ",
            "FROM",
            "(",
            "SELECT",
            "t1.*,student.linkman_phone, sum( IFNULL(t2.residue_course_count,0) + IFNULL(t6.all_course_count,0)) AS residue_course_count_all",
            "FROM",
            "student_grade_view AS t1",
            "LEFT JOIN",
            "<when test='isHistory == 0'>",
            "order_detail AS t2",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history AS t2",
            "</when>",
            "ON t2.student_id = t1.student_id ",
            "AND t2.school_id = t1.school_id ",
            "AND t2.subjects_id = t1.subjects_id ",
            "AND t2.year_part = t1.year_part ",
            "AND t2.lesson_type = t1.lesson_type ",
            "AND t2.quarter_num = t1.quarter_num ",
            "AND t2.STATUS &lt;&gt; 2 ",
            "AND t2.deleted = 0",
            "LEFT JOIN order_detail AS t6 ON t6.student_id = t1.student_id ",
            "AND t6.subjects_id = t1.subjects_id ",
            "AND t6.lesson_type = t1.lesson_type ",
            "AND t6.year_and_quarter > ${nowYearAndQuarter} ",
            "AND t6.year_and_quarter &lt;= ${nextYearAndQuarter} ",
            "AND t6.STATUS &lt;&gt; 2 ",
            "AND t6.deleted = 0 ",
            "AND t6.order_type = 1",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "LEFT JOIN student ON student.id = t1.student_id ",
            "WHERE",
            "t1.year_part = ${yearPart}",
            "and t7.id is null",
            "AND t1.quarter_num = ${quarterNum}",
            "and t1.is_last = 1",
            "and t1.is_makeup = 0",
            "and t1.deleted = 0",
            "and t1.status = 1",
            "and t1.lesson_type = ${lessonType}",
            "and (t1.reading_status in(1,4,5,6) or (t1.reading_status=3 and t1.use_course_count&gt;0))",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and t1.subjects_id = #{subjectsId}",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t1.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t1.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and t1.year_class_id &lt; 10",
            "</when>",
            "<otherwise>",
            "  and t1.year_class_id &lt; 10",
            "</otherwise>",
            "</choose>",
            "</when>",
            "GROUP BY",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.teacher_id",
            ") AS t",
            "INNER JOIN (",
            "SELECT",
            "td.*,",
            "tm.teacher_id,",
            "tm.team_name,",
            "tm.id AS team_id,",
            "tm.pid AS team_pid",
            "FROM",
            "track_detail AS td",
            "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE",
            "tm.classify = ${classify} ",
            "and tm.track_type = ${trackType} ",
            "AND tm.status = 1 ",
            "AND tm.deleted = 0 ",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
            "  and tm.id in (${teamId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and tm.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "GROUP BY",
            "td.track_id,",
            "td.sourse_id ",
            ") AS t02 ON t.teacher_id = t02.sourse_id ",
            "AND ( find_in_set( t.year_class_id, t02.year_class_id_str ) OR t02.year_class_id_str = '' ) ",
            "AND ( find_in_set( t.school_id, t02.school_id_str ) OR t02.school_id_str = '' ) ",
            "AND ( find_in_set( t.course_type_id, t02.class_type_id_str ) OR t02.class_type_id_str = '' ) ",
            ") AS t3 where 1=1",
            "and teacher_id=#{teacherId}",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "and grade_id=#{gradeId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and subjects_id=#{subjectsId}",
            "</when>",
            "<when test='detailType==1'>",
            "and residue_course_count &gt;= ${feeCourseNum}",
            "</when>",
            "<when test='detailType==2'>",
            "and residue_course_count &lt; ${feeCourseNum}",
            "</when>",
            "<when test='detailType==3'>",
            "and teacher_id=0",
            "</when>",
            "</script>"})
    public abstract IPage<StudentRatePay> getPayFeeDetailMarket(IPage<?> page,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") String schoolId,@Param("teacherId") String teacherId,@Param("gradeId") String gradeId,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("detailType") String detailType,@Param("isHistory") Integer isHistory,@Param("lessonType") String lessonType,@Param("nowYearAndQuarter") Integer nowYearAndQuarter,@Param("nextYearAndQuarter") Integer nextYearAndQuarter,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("feeCourseNum") Integer feeCourseNum,@Param("marketTeacherId") String marketTeacherId,@Param("trackType") Integer trackType,@Param("classify") Integer classify,@Param("teamId") String teamId);

    @Select({"<script>",
            "SELECT ",
            "count( id ) AS studentAllCount,",
            "SUM( CASE WHEN residue_course_count &gt;= ${feeCourseNum} THEN 1 ELSE 0 END ) AS bigFifteen, ",
            "SUM( CASE WHEN residue_course_count &lt; ${feeCourseNum} THEN 1 ELSE 0 END ) AS littleFifteen,",
            "school_id,",
            "getSchoolName ( school_id ) AS schoolName, ",
            "subjects_id,",
            "teacher_id, ",
            "getStaffNameForId ( teacher_id ) AS teacherName, ",
            "market_teacher_id, ",
            "getStaffNameForId ( market_teacher_id ) AS marketTeacherName,",
            "getSubjectsName ( subjects_id ) AS subjects_name,",
            "team_name, ",
            "team_id, ",
            "team_pid ",
            "FROM ",
            "( ",
            "SELECT ",
            "t01.id,",
            "t01.school_id, ",
            "t01.teacher_id, ",
            "t01.student_id, ",
            "t01.subjects_id, ",
            "t01.year_part, ",
            "t01.year_class_id, ",
            "t01.course_type_id, ",
            "t01.quarter_num, ",
            "t01.grade_id, ",
            "t01.lesson_type, ",
            "t01.residue_course_count, ",
            "t02.teacher_id AS market_teacher_id, ",
            "t02.team_name, ",
            "t02.team_id, ",
            "t02.team_pid ",
            "FROM ",
            "( ",
            "SELECT ",
            "src.*,g.year_class_id,g.course_type_id ",
            "FROM ",
            "student_residue_course AS src ",
            "LEFT JOIN grade AS g ON src.grade_id = g.id ",
            "WHERE ",
            "src.lesson_type = ${lessonType} ",
            "AND src.reading_status = 1 ",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "and src.grade_id=#{gradeId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND src.subjects_id = ${subjectsId} ",
            "</when>",
            "AND src.start_date &gt;= '${startDate}' ",
            "AND src.end_date &lt;= '${endDate}' ",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and g.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "AND g.year_class_id &lt; 13",
            "</when>",
            "<otherwise>",
            "AND g.year_class_id &lt; 13",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "AND g.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "AND src.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='type ==3'>",
            "  and src.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</when>",
            "GROUP BY",
            "src.school_id,src.student_id,src.subjects_id,src.teacher_id ",
            ") AS t01 ",
            "INNER JOIN ( ",
            "SELECT ",
            "td.*, ",
            "tm.teacher_id,tm.team_name,tm.id AS team_id,tm.pid AS team_pid ",
            "FROM ",
            "track_detail AS td ",
            "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE ",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType} ",
            "AND tm.`status` = 1 ",
            "AND tm.deleted = 0 ",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
            "AND tm.id in (${teamId}) ",
            "</when>",
            "GROUP BY ",
            "td.track_id,td.sourse_id ",
            ") AS t02 ON t01.teacher_id = t02.sourse_id ",
            "AND ( find_in_set( t01.year_class_id, t02.year_class_id_str ) OR t02.year_class_id_str = '' ) ",
            "AND ( find_in_set( t01.school_id, t02.school_id_str ) OR t02.school_id_str = '' ) ",
            "AND ( find_in_set( t01.course_type_id, t02.class_type_id_str ) OR t02.class_type_id_str = '' ) ",
            ") AS t1 ",
            " where 1=1",
            "and teacher_id=#{teacherId}",
            "<when test='detailType==1'>",
            "and residue_course_count &gt;= ${feeCourseNum}",
            "</when>",
            "<when test='detailType==2'>",
            "and residue_course_count &lt; ${feeCourseNum}",
            "</when>",
            "<when test='detailType==3'>",
            "and teacher_id=0",
            "</when>",
            "GROUP BY ",
            "team_id,teacher_id",
            "</script>"})
    public abstract IPage<StudentRatePay> getPayFeeDetailMarketByDate(IPage<?> page,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") String schoolId,@Param("teacherId") String teacherId,@Param("gradeId") String gradeId,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("detailType") String detailType,@Param("lessonType") String lessonType,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("feeCourseNum") Integer feeCourseNum,@Param("trackType") Integer trackType,@Param("classify") Integer classify,@Param("teamId") String teamId);


    /**
     * 获取已缴详情
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param teacherId
     * @param gradeId
     * @param subjectsId
     * @param type
     * @param startDate
     * @param endDate
     * @param trackId
     * @param detailType
     * @return
     */
    @Select({"<script>",
            "SELECT * FROM (",
            "select *,IFNULL( sum( CASE WHEN num >= 1 THEN num ELSE 0 END ), 0 ) AS all_num ",
            "from (",
            "SELECT t1.*,sum(IFNULL( t2.residue_course_count, 0 ) + IFNULL( t6.all_course_count, 0 )) AS residue_course_count ",
            " FROM rate_pay_view AS t1 ",
            "LEFT JOIN",
            "<when test='isHistory == 0'>",
            "order_detail AS t2",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history AS t2",
            "</when>",
            "ON t2.student_id = t1.student_id ",
            "AND t2.school_id = t1.school_id ",
            "AND t2.subjects_id = t1.subjects_id ",
            "AND t2.year_part = t1.year_part ",
            "AND t2.lesson_type = t1.lesson_type ",
            "AND t2.quarter_num = t1.quarter_num ",
            "AND t2.STATUS &lt;&gt; 2 ",
            "AND t2.deleted = 0",
            "LEFT JOIN order_detail AS t6 ON t6.student_id = t1.student_id ",
            "AND t6.subjects_id = t1.subjects_id ",
            "AND t6.lesson_type = t1.lesson_type ",
            "AND t6.year_and_quarter > ${nowYearAndQuarter} ",
            "AND t6.year_and_quarter &lt;= ${nextYearAndQuarter} ",
            "AND t6.STATUS &lt;&gt; 2 ",
            "AND t6.deleted = 0 ",
            "AND t6.order_type = 1",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE",
            "t1.year_part = ${payYearPart}",
            "and t7.id is null",
            "and t1.quarter_num = ${payQuarterNum}",
            "and t1.lesson_type = ${lessonType}",
            "and t1.deleted = 0",
            "and t1.status = 1",
            "and t1.lesson_type = ${lessonType}",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and t1.year_class_id &lt; 10",
//            "  and t1.year_class_id &lt; 13",
            //"  and t1.year_class_id &lt;&gt; 6",
            "</when>",
            "<otherwise>",
            "  and t1.year_class_id &lt; 10",
//            "  and t1.year_class_id &lt; 13",
            //"  and t1.year_class_id &lt;&gt; 6",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='type == 1 or type == 3'>",
            "and t1.teacher_id=#{teacherId}",
            "</when>",
            "<when test='type == 2'>",
            "and t1.teacher_id=#{teacherId} and t1.school_id = #{schoolId}",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t1.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t1.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='type == 4'>",
            "and t1.school_id=#{schoolId}",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "and t1.grade_id=#{gradeId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and t1.subjects_id=#{subjectsId}",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos;'>",
            "and t1.add_date &gt;= #{startDate}",
            "</when>",
            "<when test='endDate!=null and endDate!=&apos;&apos;'>",
            "and t1.add_date &lt;= #{endDate}",
            "</when>",
            "group by t1.student_id,t1.subjects_id,t1.order_detail_id",
            ") as t group by student_id,subjects_id",
            ") AS t01 WHERE all_num &gt;= ${periodsNum}",
            "</script>"})
    public abstract IPage<StudentRatePay> getRatePayFeeDetail(IPage<?> page,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") String schoolId,@Param("teacherId") String teacherId,@Param("gradeId") String gradeId,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("detailType") String detailType,@Param("isHistory") Integer isHistory,@Param("lessonType") String lessonType,@Param("nowYearAndQuarter") Integer nowYearAndQuarter,@Param("nextYearAndQuarter") Integer nextYearAndQuarter,@Param("payYearPart") Integer payYearPart,@Param("payQuarterNum") Integer payQuarterNum,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("periodsNum") Integer periodsNum);


    /**
     * 市场部 - 获取已缴详情
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param teacherId
     * @param gradeId
     * @param subjectsId
     * @param type
     * @param startDate
     * @param endDate
     * @param trackId
     * @param detailType
     * @return
     */
    @Select({"<script>",
            "SELECT * FROM (",
            "select *,IFNULL( sum( CASE WHEN num &gt;= 1 THEN num ELSE 0 END ), 0 ) AS all_num ",
            "from (",
            "SELECT t1.*,sum(IFNULL( t2.residue_course_count, 0 ) + IFNULL( t6.all_course_count, 0 )) AS residue_course_count ",
            " FROM rate_pay_view AS t1 ",
            "INNER JOIN (",
            "SELECT",
            "td.*,",
            "tm.teacher_id ",
            "FROM",
            "track_detail AS td",
            "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "and tm.id in(${teamId})",
            "WHERE",
            "tm.classify = ${classify} ",
            "and tm.track_type = ${trackType} ",
            "AND tm.status = 1 ",
            "AND tm.deleted = 0 ",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and tm.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "GROUP BY",
            "td.track_id,",
            "td.sourse_id ",
            ") AS t02 ON t1.teacher_id = t02.sourse_id ",
            "AND ( find_in_set( t1.year_class_id, t02.year_class_id_str ) OR t02.year_class_id_str = '' )",
            "AND ( find_in_set( course_type_id, t02.class_type_id_str ) OR t02.class_type_id_str = '' ) ",
            "AND ( find_in_set( t1.school_id, t02.school_id_str ) OR t02.school_id_str = '' ) ",
            "LEFT JOIN",
            "<when test='isHistory == 0'>",
            "order_detail AS t2",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history AS t2",
            "</when>",
            "ON t2.student_id = t1.student_id ",
            "AND t2.school_id = t1.school_id ",
            "AND t2.subjects_id = t1.subjects_id ",
            "AND t2.year_part = t1.year_part ",
            "AND t2.lesson_type = t1.lesson_type ",
            "AND t2.quarter_num = t1.quarter_num ",
            "AND t2.STATUS &lt;&gt; 2 ",
            "AND t2.deleted = 0",
            "LEFT JOIN order_detail AS t6 ON t6.student_id = t1.student_id ",
            "AND t6.subjects_id = t1.subjects_id ",
            "AND t6.lesson_type = t1.lesson_type ",
            "AND t6.year_and_quarter > ${nowYearAndQuarter} ",
            "AND t6.year_and_quarter &lt;= ${nextYearAndQuarter} ",
            "AND t6.STATUS &lt;&gt; 2 ",
            "AND t6.deleted = 0 ",
            "AND t6.order_type = 1",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE",
            "t1.year_part = ${payYearPart}",
            "and t7.id is null",
            "and t1.quarter_num = ${payQuarterNum}",
            "and t1.lesson_type = ${lessonType}",
            "and t1.deleted = 0",
            "and t1.status = 1",
            "and t1.lesson_type = ${lessonType}",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and t1.year_class_id &lt; 10",
            "</when>",
            "<otherwise>",
            "  and t1.year_class_id &lt; 10",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='type == 1 or type == 3'>",
            "and t1.teacher_id=#{teacherId}",
            "</when>",
            "<when test='type == 2'>",
            "and t1.teacher_id=#{teacherId} and t1.school_id = #{schoolId}",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t1.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t1.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='type == 4'>",
            "and t1.school_id=#{schoolId}",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "and t1.grade_id=#{gradeId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and t1.subjects_id=#{subjectsId}",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos;'>",
            "and t1.add_date &gt;= #{startDate}",
            "</when>",
            "<when test='endDate!=null and endDate!=&apos;&apos;'>",
            "and t1.add_date &lt;= #{endDate}",
            "</when>",
            "group by t1.student_id,t1.subjects_id,t1.order_detail_id",
            ") as t group by student_id,subjects_id",
            ") AS t01 WHERE all_num &gt;= ${periodsNum}",
            "</script>"})
    public abstract IPage<StudentRatePay> getRatePayFeeDetailMarket(IPage<?> page,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") String schoolId,@Param("teacherId") String teacherId,@Param("gradeId") String gradeId,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("detailType") String detailType,@Param("isHistory") Integer isHistory,@Param("lessonType") String lessonType,@Param("nowYearAndQuarter") Integer nowYearAndQuarter,@Param("nextYearAndQuarter") Integer nextYearAndQuarter,@Param("payYearPart") Integer payYearPart,@Param("payQuarterNum") Integer payQuarterNum,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("marketTeacherId") String marketTeacherId,@Param("trackType") Integer trackType,@Param("classify") Integer classify,@Param("teamId") String teamId,@Param("periodsNum") Integer periodsNum);


    /**
     * 市场部 - 获取已缴详情
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param teacherId
     * @param gradeId
     * @param subjectsId
     * @param type
     * @param startDate
     * @param endDate
     * @param trackId
     * @param detailType
     * @return
     */
    @Select({"<script>",
            "SELECT * FROM (",
            "select *,IFNULL( sum( CASE WHEN num &gt;= 1 THEN num ELSE 0 END ), 0 ) AS all_num ",
            "from (",
            "SELECT t1.*,sum(IFNULL( t2.residue_course_count, 0 ) + IFNULL( t6.all_course_count, 0 )) AS residue_course_count ",
            " FROM rate_pay_view AS t1 ",
            "INNER JOIN (",
            "SELECT",
            "td.*,",
            "tm.teacher_id ",
            "FROM",
            "track_detail_historry AS td",
            "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "and tm.id in(${teamId})",
            "WHERE",
            "tm.classify = ${classify} ",
            "and tm.track_type = ${trackType} ",
            "and '${endDate}' BETWEEN td.start_date AND td.end_date ",
            "AND tm.status = 1 ",
            "AND tm.deleted = 0 ",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and tm.teacher_id in(select sourse_id from track_detail_historry where deleted = 0 AND '${endDate}' BETWEEN track_detail_historry.start_date AND track_detail_historry.end_date and track_id =#{trackId})",
            "</when>",
            "GROUP BY",
            "td.track_id,",
            "td.sourse_id ",
            ") AS t02 ON t1.teacher_id = t02.sourse_id ",
            "AND ( find_in_set( t1.year_class_id, t02.year_class_id_str ) OR t02.year_class_id_str = '' )",
            "AND ( find_in_set( course_type_id, t02.class_type_id_str ) OR t02.class_type_id_str = '' ) ",
            "AND ( find_in_set( t1.school_id, t02.school_id_str ) OR t02.school_id_str = '' ) ",
            "LEFT JOIN",
            "<when test='isHistory == 0'>",
            "order_detail AS t2",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history AS t2",
            "</when>",
            "ON t2.student_id = t1.student_id ",
            "AND t2.school_id = t1.school_id ",
            "AND t2.subjects_id = t1.subjects_id ",
            "AND t2.year_part = t1.year_part ",
            "AND t2.lesson_type = t1.lesson_type ",
            "AND t2.quarter_num = t1.quarter_num ",
            "AND t2.STATUS &lt;&gt; 2 ",
            "AND t2.deleted = 0",
            "LEFT JOIN order_detail AS t6 ON t6.student_id = t1.student_id ",
            "AND t6.subjects_id = t1.subjects_id ",
            "AND t6.lesson_type = t1.lesson_type ",
            "AND t6.year_and_quarter > ${nowYearAndQuarter} ",
            "AND t6.year_and_quarter &lt;= ${nextYearAndQuarter} ",
            "AND t6.STATUS &lt;&gt; 2 ",
            "AND t6.deleted = 0 ",
            "AND t6.order_type = 1",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.deleted=0 and t7.type =1 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)","WHERE",
            "t1.year_part = ${payYearPart}",
            "and t7.id is null",
            "and t1.quarter_num = ${payQuarterNum}",
            "and t1.lesson_type = ${lessonType}",
            "and t1.deleted = 0",
            "and t1.status = 1",
            "and t1.lesson_type = ${lessonType}",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and t1.year_class_id &lt; 10",
            "</when>",
            "<otherwise>",
            "  and t1.year_class_id &lt; 10",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='type == 1 or type == 3'>",
            "and t1.teacher_id=#{teacherId}",
            "</when>",
            "<when test='type == 2'>",
            "and t1.teacher_id=#{teacherId} and t1.school_id = #{schoolId}",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and t1.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and t1.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='type == 4'>",
            "and t1.school_id=#{schoolId}",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "and t1.grade_id=#{gradeId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and t1.subjects_id=#{subjectsId}",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos;'>",
            "and t1.add_date &gt;= #{startDate}",
            "</when>",
            "<when test='endDate!=null and endDate!=&apos;&apos;'>",
            "and t1.add_date &lt;= #{endDate}",
            "</when>",
            "group by t1.student_id,t1.subjects_id,t1.order_detail_id",
            ") as t group by student_id,subjects_id",
            ") AS t01 WHERE all_num &gt;= ${periodsNum}",
            "</script>"})
    public abstract IPage<StudentRatePay> getRatePayFeeDetailMarketHistory(IPage<?> page,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") String schoolId,@Param("teacherId") String teacherId,@Param("gradeId") String gradeId,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("detailType") String detailType,@Param("isHistory") Integer isHistory,@Param("lessonType") String lessonType,@Param("nowYearAndQuarter") Integer nowYearAndQuarter,@Param("nextYearAndQuarter") Integer nextYearAndQuarter,@Param("payYearPart") Integer payYearPart,@Param("payQuarterNum") Integer payQuarterNum,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("marketTeacherId") String marketTeacherId,@Param("trackType") Integer trackType,@Param("classify") Integer classify,@Param("teamId") String teamId,@Param("periodsNum") Integer periodsNum);

    /**
     * 财务报表
     * @param page
     * @param schoolId
     * @param subjectsId
     * @param yearAndQuarter
     * @return
     */
    @Select({"<script>",
            "select",
            "a.student_id,a.student_name,a.grade_id,a.year_class_id,a.subjects_id,SUM(a.all_course_count-a.roll_out_house) as all_course_count,SUM(a.use_course_count) as use_course_count,SUM(a.residue_course_count) as residue_course_count,SUM(a.price-a.roll_out_price) as price,SUM(a.discounts_price) as discounts_price,SUM(residue_price) as residue_price,SUM(a.out_price + a.turn_out_price) as out_price,SUM(a.solid_price-a.roll_out_price - a.turn_out_price) as solid_price,SUM(a.use_price+a.abnormal_price) as reality_pirce,sum(a.abnormal_price) as abnormal_price,deleted,status,order_type,year_and_quarter,id,school_id",
            "from order_detail_statement_view as a ",
            "where",
            "deleted = 0 and `status`&lt;&gt;2",
            "and order_type in(1,3,5)",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and school_id=${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and subjects_id=${subjectsId}",
            "</when>",
            "<when test='yearAndQuarter!=null and yearAndQuarter!=&apos;&apos;'>",
            "and year_and_quarter&gt;=${yearAndQuarter}",
            "</when>",
            "<when test='studentName!=null and studentName!=&apos;&apos;'>",
            "and student_name like &apos;%${studentName}%&apos;",
            "</when>",
            "GROUP BY",
            "school_id",
            "</script>"})
    public abstract IPage<StatementData> statementData(IPage<?> page, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId, @Param("yearAndQuarter") Integer yearAndQuarter, @Param("studentName")String studentName);

    @Select({"<script>",
            "SELECT",
            "*,",
            "getStaffNameForId(teacher_id) as teacher_name,",
            "g.`name` AS grade_name,",
            "sum( CASE WHEN residue_course_count_all = 0 AND use_course_count &gt; 0 AND turn_out_house = 0 THEN 1 ELSE 0 END ) AS losse_student_count,",
            "sum( CASE WHEN use_course_count &gt; 0 THEN 1 ELSE 0 END ) AS student_all_count",
            "FROM",
            "(",
            "SELECT",
            "t.*,",
            "getSchoolName ( t.school_id ) AS school_name,",
            "t1.`name` AS student_name,",
            "getSubjectsName ( t.subjects_id ) AS subjects_name,",
            "t2.grade_id,",
            "(IFNULL( t.residue_course_count, 0 ) + IFNULL( t6.all_course_count, 0 )) AS residue_course_count_all",
            "FROM",
            "(",
            "SELECT",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count ) AS residue_course_count,",
            "SUM( use_course_count ) AS use_course_count,",
            "SUM( turn_out_house ) AS turn_out_house ",
            "FROM",
            "<when test='isHistory == 0'>",
            "order_detail",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history",
            "</when>",
            "WHERE",
            "deleted = 0 ",
            "AND `status` &lt;&gt; 2 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "<when test='yearClassIdStr!=null and yearClassIdStr!=&apos;&apos; '>",
            "AND `year_class_id` in(${yearClassIdStr})" ,
            "</when>",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id ",
            ") AS t",
            "INNER JOIN (",
            "SELECT",
            "* ",
            "FROM",
            "student_grade ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND `quarter_num` = ${quarterNum} ",
            "AND deleted = 0 ",
            "AND lesson_type = ${lessonType} ",
            "AND is_last = 1 ",
            "and status =1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON t.school_id = t2.school_id ",
            "AND t.subjects_id = t2.subjects_id ",
            "AND t.student_id = t2.student_id ",
            "AND t.lesson_type = t2.lesson_type",
            "INNER JOIN student AS t1 ON t.student_id = t1.id ",
            "LEFT JOIN (",
            "SELECT",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            //"SUM( residue_course_count ) AS residue_course_count,",
            "SUM( CASE WHEN year_and_quarter &lt;= ${nextYearAndQuarter} AND order_type = 1 THEN residue_course_count WHEN year_and_quarter = ${nextYearAndQuarter} THEN residue_course_count ELSE 0 END ) AS residue_course_count,",
            "SUM( use_course_count ) AS use_course_count,",
            "SUM( turn_out_house ) AS turn_out_house,",
            "SUM( all_course_count ) AS all_course_count ",
            "FROM",
            "order_detail ",
            "WHERE 1=1",
            "AND year_and_quarter > ${nowYearAndQuarter} ",
            "AND year_and_quarter &lt;= ${nextYearAndQuarter} ",
            "and STATUS &lt;&gt; 2 ",
            "and deleted = 0 ",
            //"and order_type = 1",
            "and lesson_type=1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id ",
            ") AS t6 ON t6.student_id = t2.student_id ",
            "AND t6.school_id = t2.school_id ",
            "AND t6.subjects_id = t2.subjects_id ",
            "AND t6.lesson_type = t2.lesson_type",
            "WHERE",
            "1=1",
            ") AS tt",
            "LEFT JOIN grade AS g ",
            "on",
            "tt.grade_id = g.id",
            "where 1=1",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and tt.subjects_id=${subjectsId}",
            "</when>",
            "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
            "AND tt.`lesson_type` = ${lessonType} ",
            "</when>",
            "<if test= 'type == 3'>",
            "and g.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</if>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and tt.school_id in(${schoolId})",
            "</when>",
            "GROUP BY",
            "<if test= 'type == 1 or type == 3'>",
            "g.teacher_id",
            "</if>",
            "<if test= 'type == 2'>",
            "tt.school_id,g.teacher_id",
            "</if>",
            "<if test= 'type == 4'>",
            "tt.school_id",
            "</if>",
            "</script>"})
    public abstract List<ArrivalRate> getLosseRade(@Param("yearPart") String yearPart,
                                                   @Param("quarterNum") String quarterNum,
                                                   @Param("schoolId") String schoolId,
                                                   @Param("subjectsId") String subjectsId,
                                                   @Param("startDate") String startDate,
                                                   @Param("endDate") String endDate,
                                                   @Param("type") String type,
                                                   @Param("lessonType") String lessonType,
                                                   @Param("isHistory") Integer isHistory,
                                                   @Param("nowYearAndQuarter") Integer nowYearAndQuarter,
                                                   @Param("nextYearAndQuarter") Integer nextYearAndQuarter,
                                                   @Param("payYearPart") Integer payYearPart,
                                                   @Param("payQuarterNum") Integer payQuarterNum,
                                                   @Param("yearClassIdStr") String yearClassIdStr);
    @Select({"<script>",
            "SELECT",
            "tt.*,",
            "g.`name` AS grade_name ",
            "FROM",
            "(",
            "SELECT",
            "t.*,",
            "getSchoolName ( t.school_id ) AS school_name,",
            "t1.`name` AS student_name,",
            "getSubjectsName ( t.subjects_id ) AS subjects_name,",
            "t2.grade_id,",
            "(IFNULL( t.residue_course_count, 0 ) + IFNULL( t6.all_course_count, 0 )) AS residue_course_count_all",
            "FROM",
            "(",
            "SELECT",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count ) AS residue_course_count,",
            "SUM( use_course_count ) AS use_course_count,",
            "SUM( turn_out_house ) AS turn_out_house ",
            "FROM",
            "<when test='isHistory == 0'>",
            "order_detail",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history",
            "</when>",
            "WHERE",
            "deleted = 0 ",
            "AND `status` &lt;&gt; 2 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "<when test='yearClassIdStr!=null and yearClassIdStr!=&apos;&apos; '>",
            "AND `year_class_id` in(${yearClassIdStr})" ,
            "</when>",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id ",
            ") AS t",
            "INNER JOIN (",
            "SELECT",
            "* ",
            "FROM",
            "student_grade ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND `quarter_num` = ${quarterNum} ",
            "AND deleted = 0 ",
            "AND lesson_type = ${lessonType} ",
            "AND is_last = 1 ",
            "and status =1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON t.school_id = t2.school_id ",
            "AND t.subjects_id = t2.subjects_id ",
            "AND t.student_id = t2.student_id ",
            "AND t.lesson_type = t2.lesson_type",
            "INNER JOIN student AS t1 ON t.student_id = t1.id ",
            "LEFT JOIN (",
            "SELECT",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            //"SUM( residue_course_count ) AS residue_course_count,",
            "SUM( CASE WHEN year_and_quarter &lt;= ${nextYearAndQuarter} AND order_type = 1 THEN residue_course_count WHEN year_and_quarter = ${nextYearAndQuarter} THEN residue_course_count ELSE 0 END ) AS residue_course_count,",
            "SUM( use_course_count ) AS use_course_count,",
            "SUM( turn_out_house ) AS turn_out_house,",
            "SUM( all_course_count ) AS all_course_count ",
            "FROM",
            "order_detail ",
            "WHERE",
            "1 = 1 ",
            "AND year_and_quarter > ${nowYearAndQuarter} ",
            "AND year_and_quarter &lt;= ${nextYearAndQuarter} ",
            "AND STATUS &lt;&gt; 2 ",
            "AND deleted = 0 ",
            "AND lesson_type = 1 ",
            //"AND order_type = 1 ",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id ",
            ") AS t6 ON t6.student_id = t2.student_id ",
            "AND t6.school_id = t2.school_id ",
            "AND t6.subjects_id = t2.subjects_id ",
            "AND t6.lesson_type = t2.lesson_type ",
            "WHERE",
            "t.use_course_count &gt; 0 and t.turn_out_house = 0",
            ") AS tt",
            "LEFT JOIN grade AS g ",
            "on",
            "tt.grade_id = g.id",
            "where 1=1",
            "AND tt.residue_course_count_all = 0",
            "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
            "AND tt.`lesson_type` = ${lessonType} " ,
            "</when>",

            "<if test= 'type == 1'>",
            "AND g.teacher_id = ${teacherId}" ,
            "</if>",
            "<if test= 'type == 2'>",
            "and tt.school_id =${schoolId}",
            "AND g.teacher_id = ${teacherId}" ,
            "</if>",
            "<if test= 'type == 3'>",
            "and g.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "AND g.teacher_id = ${teacherId}",
            "</if>",
            "<if test= 'type == 4'>",
            "and tt.school_id =${schoolId}",
            "</if>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and tt.subjects_id =${subjectsId}",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
            "AND tt.`grade_id` = ${gradeId} " ,
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
            "AND g.`teacher_id` = ${teacherId} " ,
            "</when>",
            "</script>"})
    public abstract List<ArrivalRate> getLosseRadeDetail(   @Param("yearPart") String yearPart,
                                                            @Param("quarterNum") String quarterNum,
                                                            @Param("schoolId") String schoolId,
                                                            @Param("teacherId") String teacherId,
                                                            @Param("gradeId") String gradeId,
                                                            @Param("subjectsId") String subjectsId,
                                                            @Param("startDate") String startDate,
                                                            @Param("endDate") String endDate,
                                                            @Param("type") String type,
                                                            @Param("lessonType") String lessonType,
                                                            @Param("isHistory") Integer isHistory,
                                                            @Param("nowYearAndQuarter") Integer nowYearAndQuarter,
                                                            @Param("nextYearAndQuarter") Integer nextYearAndQuarter,
                                                            @Param("payYearPart") Integer payYearPart,
                                                            @Param("payQuarterNum") Integer payQuarterNum,
                                                            @Param("yearClassIdStr") String yearClassIdStr);


    @Select({"<script>",
            "SELECT",
            "*,",
            "getSchoolName ( school_id ) AS school_name,",
            "getStaffNameForId ( teacher_id ) AS teacher_name,",
            "count(id) as student_number,",
            "SUM( CASE WHEN residue_course_count &lt;= 9 and residue_course_count &gt;0 THEN 1 ELSE 0 END ) AS residue_number ",
            "FROM",
            "(",
            "SELECT",
            "t2.id,",
            "t1.school_id,",
            "t1.teacher_id,",
            "t1.grade_year_class_id,",
            "t2.student_id,",
            "t2.subjects_id,",
            "t2.lesson_type,",
            "t2.residue_course_count ",
            "FROM",
            "(",
            "SELECT",
            "student_grade.*,",
            "grade.year_class_id AS grade_year_class_id, ",
            "grade.teacher_id ",
            "FROM",
            "student_grade",
            "LEFT JOIN grade ON student_grade.grade_id = grade.id ",
            "WHERE",
            "student_grade.year_part = ${yearPart} ",
            "AND student_grade.quarter_num = ${quarterNum} ",
            "AND student_grade.deleted = 0 ",
            "AND student_grade.STATUS = 1 ",
            "AND student_grade.is_last = 1 ",
            "AND student_grade.is_makeup &lt;&gt; 1 ",
            "AND student_grade.lesson_type = 1 ",
            "GROUP BY",
            "student_grade.school_id,",
            "student_grade.student_id,",
            "student_grade.subjects_id,",
            "student_grade.lesson_type ",
            ") AS t1",
            "LEFT JOIN (",
            "SELECT",
            "id,",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count ) AS residue_course_count ",
            "FROM",
            "order_detail ",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON t1.school_id = t2.school_id ",
            "AND t1.student_id = t2.student_id ",
            "AND t1.subjects_id = t2.subjects_id ",
            "AND t1.lesson_type = t2.lesson_type ",
            ") AS t ",
            "WHERE 1=1",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and school_id in(${schoolId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and subjects_id=${subjectsId}",
            "</when>",
            "<if test= 'type == 3'>",
            "and teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</if>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "and grade_year_class_id IN (${yearClassId})",
            "</when>",
            "GROUP BY",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id",
            "</if>",
            "<if test= 'type == 2'>",
            "school_id,teacher_id",
            "</if>",
            "<if test= 'type == 4'>",
            "school_id",
            "</if>",
            "</script>"})
    public abstract List<ResidueStatics> residueStudentCount(@Param("schoolId") String schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("lessonType") Integer lessonType,@Param("yearClassId") String yearClassId);


    @Select({"<script>",
            "SELECT",
            "t.*,",
            "t.residue_course_count as residue_number,",
            "getSchoolName ( t.school_id ) AS school_name,",
            "t5.`name` as student_name,",
            "getStaffNameForId(teacher_id) as teacher_name,",
            "getDictName(t.year_class_id, 14) as nian_ji",
            "FROM",
            "(",
            "SELECT",
            "t2.id,",
            "t2.school_id,",
            "t1.teacher_id,",
            "t1.grade_year_class_id,",
            "t2.student_id,",
            "t2.subjects_id,",
            "t2.lesson_type,",
            "t2.residue_course_count,",
            "t1.grade_id,",
            "t1.year_class_id",
            "FROM",
            "(",
            "SELECT",
            "student_grade.*,",
            "grade.year_class_id AS grade_year_class_id, ",
            "grade.teacher_id ",
            "FROM",
            "   student_grade",
            "LEFT JOIN grade ON student_grade.grade_id = grade.id ",
            "WHERE",
            "student_grade.year_part = ${yearPart} ",
            "AND student_grade.quarter_num = ${quarterNum} ",
            "AND student_grade.deleted = 0 ",
            "AND student_grade.STATUS = 1 ",
            "AND student_grade.is_last = 1 ",
            "AND student_grade.is_makeup &lt;&gt; 1 ",
            "AND student_grade.lesson_type = 1 ",
            "GROUP BY",
            "student_grade.school_id,",
            "student_grade.student_id,",
            "student_grade.subjects_id,",
            "student_grade.lesson_type ",
            ") AS t1",
            "LEFT JOIN (",
            "SELECT",
            "id,",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count ) AS residue_course_count ",
            "FROM",
            "order_detail ",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON t1.school_id = t2.school_id ",
            "AND t1.student_id = t2.student_id ",
            "AND t1.subjects_id = t2.subjects_id ",
            "AND t1.lesson_type = t2.lesson_type ",
            ") AS t",
            "LEFT JOIN student AS t5 ON t.student_id = t5.id",
            "where 1=1",
            "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
            "AND t.`lesson_type` = ${lessonType} " ,
            "</when>",
            "<if test= 'type == 1'>",
            "AND t.teacher_id = ${teacherId}" ,
            "</if>",
            "<if test= 'type == 2'>",
            "and t.school_id =${schoolId}",
            "AND t.teacher_id = ${teacherId}" ,
            "</if>",
            "<if test= 'type == 3'>",
            "and t.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "AND t.teacher_id = ${teacherId}",
            "</if>",
            "<if test= 'type == 4'>",
            "and t.school_id =${schoolId}",
            "</if>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t.subjects_id =${subjectsId}",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
            "AND t.`grade_id` = ${gradeId} " ,
            "</when>",
            "<when test='status!=null and status!=&apos;&apos; and status == 1'>",
            "and t.residue_course_count &lt;=9 and residue_course_count &gt;0",
            "</when>",
            "<when test='status == 0'>",
            "and (t.residue_course_count &gt;9 or t.residue_course_count=0)",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and t.grade_year_class_id IN (${yearClassId})",
            "</when>",
            "</script>"})
    public abstract IPage<ResidueStudent> residueStudentCountDetail(IPage<?> page, @Param("schoolId")String schoolId, @Param("subjectsId")String subjectsId, @Param("yearPart")String yearPart, @Param("quarterNum")String quarterNum, @Param("gradeId")String gradeId, @Param("teacherId")String teacherId, @Param("type")String type, @Param("status")Integer status, @Param("lessonType")Integer lessonType,@Param("yearClassId") String yearClassId);


    /**
     * 获取所报读科目的剩余课时
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select ifnull(sum(residue_course_count),0) as residue_course_count from order_detail where deleted=0 and status<>2 and school_id=#{schoolId} and student_id=#{studentId} and subjects_id=#{subjectsId} and residue_course_count>0 and year_and_quarter>=${yearAndQuarter} and lesson_type=${lessonType}")
    public abstract Integer getResidueCourseCountForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);


    @Select("select * from order_detail where year_part = ${yearPart} and quarter_num = ${quarterNum} and lesson_type =${lessonType} and student_id = ${studentId} and subjects_id =${subjectsId} and deleted = 0 ORDER BY id desc limit 1")
    public abstract OrderDetail getLastOneForStudent(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType);

    /**
     * 数据汇总
     */
    @Select("${sqlDataSummary}")
    public abstract List<RenewClassRecord> getDataSummary(@Param("sqlDataSummary") String sqlDataSummary);


    @Select({"<script>",
            "SELECT",
            "school_id,",
            "schoolName,",
            "subjects_id,",
            "teacher_id,",
            "teacherName,",
            "market_teacher_id,",
            "marketTeacherName,",
            "team_name,",
            "team_id,",
            "team_pid,",
            "subjects_name",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
            ",studentAllCount,",
            "bigFifteen,",
            "littleFifteen,",
            "payNum",
            "</when>",
            "<when test='teamId==null or teamId==&apos;&apos; '>",
            ",sum(studentAllCount) as studentAllCount,",
            "sum(bigFifteen) as bigFifteen,",
            "sum(littleFifteen) as littleFifteen,",
            "sum(payNum) as payNum",
            "</when>",
            "FROM",
            "(",
            "SELECT",
            "count( id ) AS studentAllCount,",
            "SUM( CASE WHEN residue_course_count &gt;= ${feeCourseNum}  THEN 1 ELSE 0 END ) AS bigFifteen,",
            "SUM( CASE WHEN residue_course_count &lt; ${feeCourseNum}  THEN 1 ELSE 0 END ) AS littleFifteen,",
            "school_id,",
            "getSchoolName ( school_id ) AS schoolName,",
            "subjects_id,",
            "teacher_id,",
            "getStaffNameForId ( teacher_id ) AS teacherName,",
            "market_teacher_id,",
            "getStaffNameForId ( market_teacher_id ) AS marketTeacherName,",
            "getSubjectsName ( subjects_id ) AS subjects_name,",
            "team_name,",
            "team_id,",
            "team_pid,",
            "(",
            "SELECT",
            "IFNULL( sum( CASE WHEN num &gt;= ${periodsNum} THEN 1 ELSE 0 END ), 0 ) AS all_num ",
            "FROM",
            "(",
            "SELECT",
            "sum( num ) AS num ",
            "FROM",
            "rate_pay_view AS rp",
            "INNER JOIN (",
            "SELECT",
            "td.*,",
            "tm.teacher_id ",
            "FROM",
            "track_detail_historry AS td",
            "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType} ",
            "AND tm.STATUS = 1 ",
            "AND tm.deleted = 0 ",
            "AND td.track_id = team_id ",
            "AND '${endDate}' BETWEEN td.start_date AND td.end_date ",
            "GROUP BY",
            "td.track_id,",
            "td.sourse_id ",
            ") AS ray_t02 ON rp.teacher_id = ray_t02.sourse_id ",
            "AND ( find_in_set( rp.year_class_id, ray_t02.year_class_id_str ) OR ray_t02.year_class_id_str = '' ) ",
            "AND ( find_in_set( rp.school_id, ray_t02.school_id_str ) OR ray_t02.school_id_str = '' ) ",
            "AND ( find_in_set( rp.course_type_id, ray_t02.class_type_id_str ) OR ray_t02.class_type_id_str = '' ) ",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.type =1 and t7.deleted=0 and rp.student_id = t7.student_id and rp.year_part = t7.year_part and rp.quarter_num = t7.quarter_num and (FIND_IN_SET(rp.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(rp.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE",
            "rp.deleted = 0 ",
            "AND rp.STATUS = 1 ",
            "AND rp.year_part = ${payYearPart} ",
            "AND rp.quarter_num = ${payQuarterNum} ",
            "AND rp.lesson_type = lesson_type ",
            "<when test='startDate!=null and startDate!=&apos;&apos;'>",
            "and rp.add_date &gt;= #{startDate}",
            "</when>",
            "<when test='endDate!=null and endDate!=&apos;&apos;'>",
            "and rp.add_date &lt;= #{endDate}",
            "</when>",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and rp.year_class_id &lt; 10",
//            "  and rp.year_class_id &lt; 13",
            //"  and rp.year_class_id &lt;&gt; 6",
            "</when>",
            "<otherwise>",
            "  and rp.year_class_id &lt; 10",
//            "  and rp.year_class_id &lt; 13",
            //"  and rp.year_class_id &lt;&gt; 6",
            "</otherwise>",
            "</choose>",
            "</when>",
            "AND rp.teacher_id = t001.teacher_id ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and rp.subjects_id = ${subjectsId}",
            "</when>",
            "GROUP BY",
            "rp.student_id,",
            "rp.subjects_id ",
            ") AS t_rate_pay ",
            ") AS payNum ",
            "FROM",
            "(",
            "SELECT",
            "t01.*,",
            "t02.teacher_id AS market_teacher_id,",
            "t02.team_name,",
            "t02.team_id,",
            "t02.team_pid ",
            "FROM",
            "(",
            "SELECT",
            "t1.*,",
            "grade.year_class_id,",
            "course_type_id ",
            "FROM",
            "student_residue_course as t1",
            "INNER JOIN grade ON t1.grade_id = grade.id ",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.type =1 and t7.deleted=0 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE",
            "t1.year_part = ${yearPart}",
            "and t7.id is null",
            "AND t1.quarter_num = ${quarterNum}",
            "and '${endDate}' BETWEEN t1.start_date AND t1.end_date ",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and grade.year_class_id &lt; 10",
//            "  and grade.year_class_id &lt; 13",
            //"  and grade.year_class_id &lt;&gt; 6",
            "</when>",
            "<otherwise>",
            "  and grade.year_class_id &lt; 10",
//            "  and grade.year_class_id &lt; 13",
            //"  and grade.year_class_id &lt;&gt; 6",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and grade.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and grade.course_type_id in(${classTypeId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and t1.subjects_id = #{subjectsId}",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "  and t1.school_id in(${schoolId})",
            "</when>",
//            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
//            "  and t1.teacher_id in(select sourse_id from track_detail_historry where deleted = 0 AND '${endDate}' BETWEEN track_detail_historry.start_date AND track_detail_historry.end_date and track_id =#{trackId})",
//            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==4'>",
            "  and t1.school_id in(select sourse_id from track_detail_historry where deleted = 0 AND '${endDate}' BETWEEN track_detail_historry.start_date AND track_detail_historry.end_date and track_id =#{trackId})",
            "</when>",
            "<when test='type ==3'>",
            "  and t1.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</when>",
            ") AS t01",
            "INNER JOIN (",
            "SELECT",
            "td.*,",
            "tm.teacher_id,",
            "tm.team_name,",
            "tm.id AS team_id,",
            "tm.pid AS team_pid ",
            "FROM",
            "track_detail_historry AS td",
            "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart}",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType} ",
            "AND tm.STATUS = 1 ",
            "AND tm.deleted = 0 ",
            "AND '${endDate}' BETWEEN td.start_date AND td.end_date ",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
            "  and tm.id in (${teamId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and tm.teacher_id in(select sourse_id from track_detail_historry where deleted = 0 AND '${endDate}' BETWEEN track_detail_historry.start_date AND track_detail_historry.end_date and track_id =#{trackId})",
            "</when>",
            "GROUP BY",
            "td.track_id,",
            "td.sourse_id ",
            ") AS t02 ON t01.teacher_id = t02.sourse_id ",
            "AND ( find_in_set( t01.year_class_id, t02.year_class_id_str ) OR t02.year_class_id_str = '' ) ",
            "AND ( find_in_set( t01.school_id, t02.school_id_str ) OR t02.school_id_str = '' ) ",
            "AND ( find_in_set( t01.course_type_id, t02.class_type_id_str ) OR t02.class_type_id_str = '' )) AS t001 ",
            "GROUP BY",
            "team_id,",
            "teacher_id ",
            ") AS t ",
            "<when test='teamId==null or teamId==&apos;&apos; '>",
            "GROUP BY team_id",
            "</when>",
            "</script>"})
    public abstract List<RenewClassRecord> getPayFeeMarketHistory(@Param("schoolId") String schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("isHistory") Integer isHistory,@Param("lessonType") Integer lessonType,@Param("nowYearAndQuarter") Integer nowYearAndQuarter,@Param("nextYearAndQuarter") Integer nextYearAndQuarter,@Param("payYearPart") Integer payYearPart,@Param("payQuarterNum") Integer payQuarterNum,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("feeCourseNum") Integer feeCourseNum,@Param("marketTeacherId") String marketTeacherId,@Param("trackType") Integer trackType,@Param("classify") Integer classify,@Param("teamId")String teamId,@Param("periodsNum") Integer periodsNum);

    @Select({"<script>",
            "SELECT",
            "* ",
            "FROM",
            "(",
            "SELECT",
            "t01.*,",
            "t02.teacher_id AS market_teacher_id,",
            "t02.team_name,",
            "t02.team_id,",
            "t02.team_pid ",
            "FROM",
            "(",
            "SELECT",
            "t1.*,",
            "grade.year_class_id,",
            "course_type_id,",
            "grade.`name` AS grade_name,",
            "stu.name as student_name,",
            "staff.name as teacher_name",
            "FROM",
            "student_residue_course AS t1",
            "INNER JOIN grade ON t1.grade_id = grade.id ",
            "LEFT JOIN student as stu on t1.student_id = stu.id ",
            "LEFT JOIN staff on t1.teacher_id = staff.id ",
            "LEFT JOIN rade_exclude_student as t7 on t7.classify_id = 1 and t7.type =1 and t7.deleted=0 and t1.student_id = t7.student_id and t1.year_part = t7.year_part and t1.quarter_num = t7.quarter_num and (FIND_IN_SET(t1.subjects_id,t7.subjects_ids) or t7.subjects_ids is null) and (FIND_IN_SET(t1.school_id,t7.school_ids) or t7.school_ids is null)",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "and t7.id is null",
            "AND t1.quarter_num =  ${quarterNum} ",
            "AND '${endDate}' BETWEEN t1.start_date AND t1.end_date ",
            "<when test='yearClassId==null or yearClassId==&apos;&apos; '>",
            "<choose>",
            "<when test='quarterNum == 2'>",
            "  and grade.year_class_id &lt; 10",
            "</when>",
            "<otherwise>",
            "  and grade.year_class_id &lt; 10",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and t1.subjects_id = #{subjectsId}",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and grade.year_class_id in(${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "  and grade.course_type_id in(${classTypeId})",
            "</when>",
            ") AS t01",
            "INNER JOIN (",
            "SELECT",
            "td.*,",
            "tm.teacher_id,",
            "tm.team_name,",
            "tm.id AS team_id,",
            "tm.pid AS team_pid ",
            "FROM",
            "track_detail_historry AS td",
            "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum}  ",
            "AND tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType} ",
            "AND tm.STATUS = 1 ",
            "AND tm.deleted = 0 ",
            "AND '${endDate}' BETWEEN td.start_date AND td.end_date",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
            "  and tm.id in (${teamId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and tm.teacher_id in(select sourse_id from track_detail_historry where deleted = 0 AND '${endDate}' BETWEEN track_detail_historry.start_date AND track_detail_historry.end_date and track_id =#{trackId})",
            "</when>",
            "GROUP BY",
            "td.track_id,",
            "td.sourse_id ",
            ") AS t02 ON t01.teacher_id = t02.sourse_id ",
            "AND ( find_in_set( t01.year_class_id, t02.year_class_id_str ) OR t02.year_class_id_str = '' ) ",
            "AND ( find_in_set( t01.school_id, t02.school_id_str ) OR t02.school_id_str = '' ) ",
            "AND ( find_in_set( t01.course_type_id, t02.class_type_id_str ) OR t02.class_type_id_str = '' )",
            ") AS t001 where 1=1",
            "and teacher_id=#{teacherId}",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "and grade_id=#{gradeId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and subjects_id=#{subjectsId}",
            "</when>",
            "<when test='detailType==1'>",
            "and residue_course_count &gt;= ${feeCourseNum}",
            "</when>",
            "<when test='detailType==2'>",
            "and residue_course_count &lt; ${feeCourseNum}",
            "</when>",
            "<when test='detailType==3'>",
            "and teacher_id=0",
            "</when>",
            "</script>"})
    public abstract IPage<StudentRatePay> getPayFeeDetailMarketHistory(IPage<?> page,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") String schoolId,@Param("teacherId") String teacherId,@Param("gradeId") String gradeId,@Param("subjectsId") String subjectsId,@Param("type") Integer type,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("trackId") String trackId,@Param("detailType") String detailType,@Param("isHistory") Integer isHistory,@Param("lessonType") String lessonType,@Param("nowYearAndQuarter") Integer nowYearAndQuarter,@Param("nextYearAndQuarter") Integer nextYearAndQuarter,@Param("yearClassId") String yearClassId,@Param("classTypeId") String classTypeId,@Param("feeCourseNum") Integer feeCourseNum,@Param("marketTeacherId") String marketTeacherId,@Param("trackType") Integer trackType,@Param("classify") Integer classify,@Param("teamId") String teamId);

    @Select("select * from (select id,order_id,SUM(discounts_price) as discounts_price,discounts_date,created_id,arrive_affirm from order_detail where lesson_type =1 and deleted=0 and `status`<>2 GROUP BY year_part,quarter_num,order_id,student_id ORDER BY id asc) as t where discounts_price>0")
    public abstract List<OrderDetail> getDisPriceList();

    /**
     * 数据汇总->续报率
     */
    @Select("${sqlDataSummaryRenewRecord}")
    public abstract List<ContinueInfo> getDataSummaryRenewRecord(@Param("sqlDataSummaryRenewRecord") String sqlDataSummaryRenewRecord);

}
